Accumulated YTD that ignores filter

[JLarkin]

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!

[tamerj1 · Accepted solution]

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] )
)

[JLarkin]

Again, thank you very much @tamerj1 !

[JLarkin]

Hi @tamerj1,

This worked! Thank you very much for your help

[tamerj1 · Accepted solution]

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] )
)

[JLarkin]

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!

[tamerj1 · Accepted solution]

Hi @JLarkin 

please try

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)
    )
)