Forum Discussion

olijo's avatar
olijo
Frequent Visitor
2 years 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
    2 years 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
    2 years 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.

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
2 years ago

olijo 

Make sure you have a Date table in your model with a continuous range of dates for all years (this includes months where no data is present in your Data table).

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

This adjusted measure should give you the correct cumulative sum

 

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn