Hi,
This is probably very easy but I cannot seem to work out how to create a formula to bring out the total Annual Budget value. The issue is the Budget Year ends in February and leap years are causing an issue.
My aim is simply to be able to bring out total costs for a given month, along with budget YTD, and the total Annual budget for comparison (March to February). So every March the Annual Budget number should rollover to the next year. When there is a leap year instead of the Annual Budget for the current year showing in the month of February it brings in the value for the Annual Budget of the following year.
This is what I have ended up with as a placeholder which works for non-leap years.
CALCULATE(‘GL Winery’[Budget Winery], DATESYTD(ENDOFYEAR(‘DateTable’[Date], “2/28”), “2/28”))
I am new to DAX so am wondering if there is another formula I could be using which does exactly what I want. I haven’t included my file as it has financial data.
Hi,
I can’t share the PBI file as it has a lot of company data but I can share an excel export of the table. This shows the issue I have.
In the table the fields are:
FY Period - Financial period (July to June - so July23 is first month of the 2024 Financial year)
Month End - Date at end of month
Winery Year - this runs from Mar-Feb (so Mar22 is start of the 2023 Winery Year)
Budget Winery - Monthly budget figure
Amount WYTD BUD - Budget figure YTD based on Winery Year
Amount WY Full BUD - Annual Budget for Winery Year you are in
So it all works fine until you get a leap year. Then the Annual Budget figure skips to next year as End of Year is stated as 28/2. If I state 29/2 then it doesn’t work for other years. I really just need a way to stipulate the EOY is the end of February. II don’t know the DAX formulas very well. If there was a formula that you could state the month number instead of a date, but I don’t know if there is. For someone used to more financial reporting tools I was sure this would be easy, as it’s standard for these tools, but I couldn’t do it in PBI.
The aim of course is so you can see how you are going against your budget YTD but also against your total Annual Budget.
Hi @jaquan42 - Power Bi only supports Date and Month as End of Year. One way to handle is to make use of If condition and check if the Year is Leap Year then call the expression with End of Year as 29/2 else 28/2.
Year Total = if(Mod(year(today()),4) = 0,CALCULATE([Total Sales],DATESYTD(Dates[Date],"2-29")),CALCULATE([Total Sales],DATESYTD(Dates[Date],"2-28")))
Hi,
That didn’t work. I’m not sure if it is because Today references actual date today so it calculated on 2023. I have a Winery month running 1-12 in the DateTable as well as a Winery Year. I may look at an alternative formula not using Date but using the Month.
Thank you very much for your suggestions as it made me think of different ways of calculating some date functions. It is not a crucial field for me at the moment so I will park this for now.