Here is the test data:
Here is the date Data:
Dates = CALENDAR(date(2022,1,1),TODAY())
Relationship between the tables:
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
Test Scenario 2: Does Not Work, Shows 40 instead of 130
Test Scenario 3: Does not work, Even when the year is the current year
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])) )
Simply you can use the following formula for the YTD.
YTD = CALCULATE(SUM(test[Totals]), YEAR(test[Date]) = YEAR(TODAY()), REMOVEFILTERS(Dates[Date]))