Forum Discussion
YTD MEASURE NOT WORKING AS INTENDED
Here is the Table named test:
Here is the table 2 which contains Dates, which is used as Slicer:
Dates = CALENDAR(date(2022,1,1),TODAY())
Link between Dates and test dataset:
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 current year
Test Scenario 2: Does Not Work, Shows 40 instead of 130 (Expected output 130)
Test Scenario 3: Does not work, Even when the year is the current year, (Expected output 130)
Hi bipowerbix
I got the desired result, please try the below measure.
-------------------------------------------------------
YTD =VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) //12-10-2022var startyear = DATE(YEAR(TODAY()),1,1) // current start year date 1-1-2023var a= if( minim >= startyear ,calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear)) ,calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear)))return a-------------------------------------------------
--------------------------------------------------------------------------
Regards,
Novil
If I answer your question, please mark my post as a solution.
9 Replies
- PawarNovil
Resolver I
Hi bipowerbix
I got the desired result, please try the below measure.
-------------------------------------------------------
YTD =VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) //12-10-2022var startyear = DATE(YEAR(TODAY()),1,1) // current start year date 1-1-2023var a= if( minim >= startyear ,calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear)) ,calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear)))return a-------------------------------------------------
--------------------------------------------------------------------------
Regards,
Novil
If I answer your question, please mark my post as a solution.
- FreemanZ
Super User
hi bipowerbix
what is your expected result for scenario 2 and 3?
- bipowerbix
Helper I
Hi FreemanZ,
The expected output for all test scenarios should be 130, that is the YTD total for the current year.
- FreemanZ
Super User
hi @
why YTD in scenario 2 and 3 are 130?
- bipowerbix
Helper I
Nice man, I will use this in future cases