topic Re: Accumulated YTD that ignores filter in DAX Commands and Tips

Accumulated YTD that ignores filter

JLarkin — Sat, 15 Oct 2022 13:42:33 GMT

Hi all,

I'm looking to include a column in a table which includes sales YTD, in a monthly view.
E.g. If I filter by August, it includes sales YTD for Jan-Aug.

This is the current formula:

Total Sales YTD = VAR RetVal = CALCULATE( SUM('Revenue'[Sales Booked Amt - at Plan Rt]), ALL('DATE_DIM'), VALUES(DATE_DIM[YR_NBR]) ) RETURN IF( MAX('DATE_DIM'[MNTH_NBR]) <= SELECTEDVALUE(DATE_DIM[MNTH_NBR]), RetVal)

The issue I have is that to include a YTD calculation, I need to exclude the month filter to have Jan-Aug.
However, this then includes September and October data. Instead, I need the range to max at selected month value

Does anyone know how I can recode this to have it exclude anything after SELECTED VALUE of the month filter?
Unfortunately, I can't create another table as I need drill down capabilities.

Thanks!

Re: Accumulated YTD that ignores filter

tamerj1 — Sat, 15 Oct 2022 15:34:22 GMT

Hi JLarkin

Please try

Total Sales YTD = CALCULATE ( SUM ( 'Revenue'[Sales Booked Amt - at Plan Rt] ), FILTER ( ALL ( 'DATE_DIM' ), 'DATE_DIM'[Month Number] <= MAX ( 'DATE_DIM'[Month Number] ) ), VALUES ( DATE_DIM[YR_NBR] ) )

Re: Accumulated YTD that ignores filter

JLarkin — Sun, 16 Oct 2022 15:42:41 GMT

Hi tamerj1,

This worked! Thank you very much for your help

Re: Accumulated YTD that ignores filter

JLarkin — Wed, 19 Oct 2022 16:48:59 GMT

Hi tamerj1, and others,

I was wondering if you could perhaps assist me with a new and similar measure.
Rather than just a Total Sales YTD, I'm looking to have a Sales YTD of last year - where we were this time last year.

Here is the code I have

Sales YTD PY = CALCULATE( SUM('Revenue'[Sales Booked Amt - at Plan Rt]), FILTER( ALL('DATE_DIM'), 'DATE_DIM'[MNTH_NBR] <= MAX('DATE_DIM'[MNTH_NBR]) && 'DATE_DIM'[YR_NBR] = (MAX('DATE_DIM'[YR_NBR])-1) ), VALUES(DATE_DIM[YR_NBR]) )

This works to an extent, in the sense that if I select 2021 and 2022 in the filters, it will show 2021 data.

But this isn't practical, as it changes the other figures. I would need it to show 2021's figures when 2022 is selected only.

Thank you in advance!

Re: Accumulated YTD that ignores filter

tamerj1 — Wed, 19 Oct 2022 18:48:19 GMT

Hi JLarkin

please try

Re: Accumulated YTD that ignores filter

JLarkin — Thu, 20 Oct 2022 10:20:42 GMT

Again, thank you very much tamerj1 !