topic Re: YTD MEASURE NOT WORKING AS INTENDED in DAX Commands and Tips

YTD MEASURE NOT WORKING AS INTENDED

bipowerbix — Tue, 11 Apr 2023 00:17:47 GMT

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)

Re: YTD MEASURE NOT WORKING AS INTENDED

FreemanZ — Mon, 10 Apr 2023 23:52:39 GMT

hi bipowerbix

what is your expected result for scenario 2 and 3?

Re: YTD MEASURE NOT WORKING AS INTENDED

bipowerbix — Tue, 11 Apr 2023 00:16:40 GMT

Hi FreemanZ,

The expected output for all test scenarios should be 130, that is the YTD total for the current year.

Re: YTD MEASURE NOT WORKING AS INTENDED

FreemanZ — Tue, 11 Apr 2023 01:48:09 GMT

hi @

why YTD in scenario 2 and 3 are 130?

Re: YTD MEASURE NOT WORKING AS INTENDED

bipowerbix — Tue, 11 Apr 2023 02:14:00 GMT

That's what we want to achieve, For 2023, We have 3 records, 40,80, and 10, which adds up to 130. Need to show 130 in card visual using DAX measure,

Re: YTD MEASURE NOT WORKING AS INTENDED

FreemanZ — Tue, 11 Apr 2023 02:32:10 GMT

try like:

Measure = SUMX( FILTER( ALL(test), test[date]=YEAR(MAX(dates[date])) && test[date]<=TODAY() ) test[Totals] )

Re: YTD MEASURE NOT WORKING AS INTENDED

bipowerbix — Tue, 11 Apr 2023 06:01:50 GMT

Doesn't Work, Gives output as blank. (Image below of Visual titled Measure)

Re: YTD MEASURE NOT WORKING AS INTENDED

FreemanZ — Tue, 11 Apr 2023 09:58:34 GMT

hi bipowerbix

there is a typo, try like:

Measure =

SUMX(

FILTER(

ALL(test),

YEAR(test[date])=YEAR(MAX(dates[date]))

&& test[date]<=TODAY()

test[Totals]

)

Re: YTD MEASURE NOT WORKING AS INTENDED

PawarNovil — Tue, 11 Apr 2023 14:37:44 GMT

Hi bipowerbix

I got the desired result, please try the below measure.

-------------------------------------------------------

YTD =

VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) //12-10-2022

var startyear = DATE(YEAR(TODAY()),1,1) // current start year date 1-1-2023

var 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.

Re: YTD MEASURE NOT WORKING AS INTENDED

bipowerbix — Thu, 13 Apr 2023 00:18:42 GMT

Nice man, I will use this in future cases