YTD Measure Carries Data Over to Next Year

[el_sa]

Hi there, 

I am facing an issue with my YTD calculation in Power BI Desktop. I have YTD measures for calculating sales for every year, 2022, 2023, 2024 and the current year. Currently, all previous year measures are carrying the data of the whole year when I apply them.

I have tried to apply filters for the given year inside my calculation, and outside, but it did not help. If you faced a similar issue, would love to learn how you solved it.

Thank you

[hnguy71]

Hi @el_sa 
Can you share your measure and a sample with expected results?

[el_sa]

Hello

Here is my current DAX Measure, DateDimension table is a date dimension. Expected output is the number of YTD users in a given year. 

YTD_2024 =

VAR MaxDate = MAX(DateDimension[Date])

RETURN

CALCULATE(

    SUM('Sales'[TypeA]),

    FILTER(

        ALL(DateDimension),

        DateDimension[Date] <= MaxDate &&        

        DateDimension[Date] >= DATE(2024, 1, 1) &&

        DateDimension[Date] <= DATE(2024, 12, 31))

[hnguy71]

Hi @el_sa 
are you attempting to do a rolling total for the year 2024?

[el_sa]

That's right

[hnguy71]

Hi @el_sa 
In that case, this should work for you:

RollingTotal_2024 = 

VAR _CurrDate = MAX(DateDimension[Date])
VAR _Year = 2024

RETURN

/*
    Find current date and use less than or equal to to get rolling total.
    Then using DatesYTD to only keep evaluation for dates within the same year
    Finally, return only year in context
*/

CALCULATE( SUM(Sales[TypeA]), DateDimension[Date] <= _CurrDate, DATESYTD(DateDimension[Date]), DateDimension[Year] = _Year )