datepowerbidaxmeasurecalculated-field

YTD Measure not working in DAX when the date slicer is set to previous year

Here is the test data:

enter image description here

Here is the date Data:

Dates = CALENDAR(date(2022,1,1),TODAY())

Relationship between the tables: enter image description here

Question: Create a Measure that shows the YTD Total amount for the current year, even if the date slicer starts from the previous year:

Here is my code:

YTD = 
VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
var startyear = DATE(YEAR(TODAY()),1,1)
return if( minim >= startyear , 
calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear))  ,  
 calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) )

Test Scenario 1 Works fine: Shows correct info, the date slicer starts at 2022, and the measure shows the YTD result of the current year

enter image description here

Test Scenario 2: Does Not Work, Shows 40 instead of 130 enter image description here

Test Scenario 3: Does not work, Even when the year is the current year enter image description here

Solution

  • The YTD formula will help you get the correct output/result.

    YTD = 
    VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
    VAR startyear = DATE(YEAR(TODAY()),1,1)
    return if( minim >= startyear , 
            CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])),  
            CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])) )

    enter image description here

    Simply you can use the following formula for the YTD.

    YTD = CALCULATE(SUM(test[Totals]), YEAR(test[Date]) = YEAR(TODAY()), REMOVEFILTERS(Dates[Date]))