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.

Angith_Nair's avatar
Angith_Nair
Icon for Continued Contributor rankContinued Contributor
2 years ago

Hi olijo 

 

Use the below DAX

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