DAX year cummulative doesn't work with blank dates

[olijo]

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!

[danextian · Accepted solution]

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]

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!

[Kedar_Pande]

@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

[Angith_Nair]

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])
        )
    )

[rajendraongole1]

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.

[danextian · Accepted solution]

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]

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 · Accepted solution]

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.