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

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 )

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure = CALCULATE(SUM('Sales'[TypeA]),datesytd(DateDimension[Date],"31/12"))

Just ensure that you drag Month and year to your visual from the DateDimension table.

Hope this helps.

[danextian · Accepted solution]

Hi @el_sa 

It seems you are trying to calculate the YTD value relative to the max date of the current year (shifted back to prior years). Please try this:

YTD Revenue Relative to the Month Selected = 
-- Define a variable to find the latest date in the selected context
VAR _MaxDate =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Total Revenue] ), -- Finds the last date where revenue exists
        ALLSELECTED ( Dates ) -- Removes any filters applied to Dates to get the full selection context
    )

-- Extract the year of the latest date found
VAR _MaxYear =
    YEAR ( _MaxDate )

-- Get the year of the current row in the Dates table
VAR _CurrentRowYear =
    MAX ( Dates[Year] ) -- The year corresponding to the current row context

-- Calculate the difference in years between the latest date's year and the current row's year
VAR _Diff = _MaxYear - _CurrentRowYear

-- Adjust the latest date to align it with the current row's year
VAR _MaxYearDate =
    EDATE ( _MaxDate, - _Diff * 12 ) -- Shifts the latest date back by the year difference in months

-- Calculate the year-to-date revenue relative to the adjusted maximum date
VAR Result =
    TOTALYTD ( 
        [Total Revenue], -- Measure to calculate the total revenue
        Dates[Date], -- The date column for YTD calculation
        Dates[Date] <= _MaxYearDate -- Additional filter to include only dates up to the adjusted max date
    )

-- Return the final calculated result
RETURN
    Result

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure = CALCULATE(SUM('Sales'[TypeA]),datesytd(DateDimension[Date],"31/12"))

Just ensure that you drag Month and year to your visual from the DateDimension table.

Hope this helps.

[hnguy71]

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

[el_sa]

That's right

[hnguy71 · Accepted solution]

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 )