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 data for 6/2023 and 12/23.
I have cummulative sum that should start every year from january
and it works when there is data for every month
formula:= CALCULATE(SUM('Data'[IZNOS7n]);DATESYTD('Data'[Date]))
 
For 2022 is good
31.03.2022.    30.06.2022.    30.09.2022.    31.12.2022.    Grand Total
      2                    5                     9                  15                     15
 
But for 2023 it is not, I get
31.03.2023.    30.06.2023.    30.09.2023.    31.12.2023.    Grand Total
       1                                          5                                            6
 
But I should get
31.03.2023.    30.06.2023.    30.09.2023.    31.12.2023.     Grand Total
      1                    1                    6                    6                       6
 
Please help me with this, I've tried so many things and none of them worked!
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.

7 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    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.

     

     

    • olijo's avatar
      olijo
      Frequent Visitor
      1 year ago

      Thanks, that obviously works in Power BI, but I am actually in SSAS tabular, using DAX, and there it doesn't work. Probably the definition of DATESYTD is different... I'll try some more...

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        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.

  • Angith_Nair's avatar
    Angith_Nair
    Icon for Continued Contributor rankContinued Contributor
    1 year 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])
        )
    )
  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year 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

  • olijo's avatar
    olijo
    Frequent Visitor
    1 year ago

    I've used seperate dates table, but in excel I didn't put date from that table, but from original table, stupid.

    Thank you so much for your effort, you saved me!