topic Re: comparing YTD data for current year (till August) with chosen year (till August) in DAX Commands and Tips

comparing YTD data for current year (till August) with chosen year (till August)

DataAnalyst_99 — Tue, 23 Aug 2022 06:42:48 GMT

Hello everyone!

I needed help to compare this year's YTD (which is till August) to any chosen previous years. I have 2 calendar tables and have created measures to compare the data for the selected years. The measures work fine. The issue comes when I am comparing 2022 with any other year. Since 2022 is only till August, ideally any other year I choose for comparison should also show me data till August. However, what I am getting is comparing data till August 2022 to the full year of the other selected year.

My measures for comparing YTD are:

Year 1 Quantity YTD =

CALCULATE(TOTALYTD(SUM('Query1'[Quantity]), 'DimDate'[Date]), DimDate[FutureDate] = "Past")

Year 2 Qty YTD =

CALCULATE(TOTALYTD(SUM('Query1'[Quantity]), 'compare year'[Date]), 'compare year'[FutureDate] = "Past", ALL(DimDate), USERELATIONSHIP('compare year'[Date], Query1[ShipDate]))

Please help!

Thanks,

Re: comparing YTD data for current year (till August) with chosen year (till August)

Anonymous — Tue, 23 Aug 2022 06:53:16 GMT

Hello,

Please try

CALCULATE(Year 1 Quantity YTD, ALL(DimDate), USERELATIONSHIP('compare year'[Date], Query1[ShipDate]),SAMEPERIODLASTYEAR(DimDate[Date]))

Re: comparing YTD data for current year (till August) with chosen year (till August)

DataAnalyst_99 — Tue, 23 Aug 2022 07:09:24 GMT

Hi Anonymous, it didnt work, in fact, it returned a blank chart. Any idea why?
Just to provide additional info, the Compare Year date table is an inactive relationship with the Query1 table. While it is essentially the same table as the DimDate table, it is not connected to it.

Re: comparing YTD data for current year (till August) with chosen year (till August)

Anonymous — Tue, 23 Aug 2022 07:14:12 GMT

DataANalyst_99

If you want to compare times you shoild have the same link on date table

Re: comparing YTD data for current year (till August) with chosen year (till August)

DataAnalyst_99 — Tue, 23 Aug 2022 07:26:09 GMT

Anonymous, you are right, but that is not my requirement. How would you be able to have 2 year slicers on the same page? Else if I just use 1 year slicer, instead of providing just the prior year's YTD, I want to choose the year for comparison. How would I be able to do that?

This is what the issue is that I am currently facing. I need to compare apples to apples when it comes to the year 2022. I tried to created a calculated column that would look at the past and future dates. But it doesnt seem to be working..

Thanks,