DAX - Running\Cumulative total and Leap Years

1
votes

My problem is that I need to calculate a running total that works like a YTD. The "year" in question is not a calendar year, it is an academic period. TOTALYTD(..."28-02") nearly does the trick, however on leap years the 29th of Feb bleeds in to the next year's window.

I've tried CALCULATE(MyMeasure, DATESBETWEEN(...,...)) and this doesn't seem to work or I am just using the functions incorrectly.

To summerise, I need an expression that works exactly like TOTALYTD() but does not have the leap yer problem.

Thanks!

1
powerpivotdax
Mark, suggest you look @ PowerPivotPro under the 'custom calendar' stuff - basically you create a calendar table that has a column of unique sequential dates which is related to your fact table. You can then add columns to your date table e.g. Fiscal Year and slice all your data by the calendar specific to your situation. – Jacob
Thank you Jacob - I'll take a look – MarkD

1 Answers

1
votes

Unfortunately, I also experienced the same problem.

I created a dates table with a check to see if the year is a leap year.

Then I created this Dax Formula: Year End = IF(MAX( Dates[LeapYear] )=0,"02/28","02/29")

This worked perfectly, but if I add this to the TOTALYTD built-in formula like this Sales YTD = TOTALYTD( [Sales], Dates[Date], ALL( Dates[Date] ), [Year End]), it does not allow it - which is stupid - hopefully, they will allow this in the future.

I then found this answer, which answers your question but also doesn't because Power BI does not cater for it yet.

"You will need to write custom YTD measures instead" as per OwenAuger