Forum Discussion

olijo's avatar
olijo
Frequent Visitor
1 year ago
Solved

DAX year cummulative doesn't work with blank dates

I have data like this: 31.03.2022.   2 30.06.2022.   3 30.09.2022.   4 31.12.2022.   6 31.03.2023.   1 30.09.2023.   5 31.03.2024.   8 30.06.2024.   2 30.09.2024.   1   and there are no da...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi olijo 

     

    As a best practice, use a separate dates table with a complete rows of dates (no missing dates in between), relate it to your fact and apply the calculation on date column from the dates table and not on from fact. Please see attached sample pbix.

     

     

  • danextian's avatar
    danextian
    1 year ago

    In SSAS Tabular, are you using a separate dates table? Those rows of dates must exist (whether with a value or not) for DATESYTD or any other time intelligence calculations to have a value for those otherwise they will just be skipped.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi olijo -The issue here is that when there are missing dates (e.g., no data for June and December 2023), the DATESYTD function skips these months in the cumulative sum, resulting in gaps in your Year-To-Date (YTD) calculation.

 

use below calculation: 

Cumulative YTD =
VAR CurrentDate = MAX('Data'[Date])
VAR CurrentYear = YEAR(CurrentDate)
RETURN
CALCULATE(
SUM('Data'[IZNOS7n]),
FILTER(
ALL('Data'),
YEAR('Data'[Date]) = CurrentYear &&
'Data'[Date] <= CurrentDate
)
)

 

Hope it works.