YTD totals year over year while being able to use the slicer filters

[Anonymous]

Hi there,

I'm trying to compare cumulative totals per week (or per month) over several years. I've managed this exactly as intended using below measure. Yet, when I apply a slicer to enable custom selections, these selections are being ignored, i.e. the totals remain regardless of my selected value in the slicer. The slicer values are taken from the same table as used in below measure. 

**bleep**.Ordered = CALCULATE(

sum(‘TABLE’[ORDER_QUANTITY]);

    Filter(

        all(‘TABLE’);

            ‘TABLE’[ProcDate] <= MAX(‘TABLE’[ProcDate]));

    Filter('PU_Operations DIM_Date';

        'PU_Operations DIM_Date'[WeekNbr]<=53)

              )

Any thoughts as to how I can have slicers being applied? (slicer values are for example product properties). 

Many thanks, 

[v-diye-msft]

Hi @Anonymous ,

What's the table PU_Operations DIM_Date, are there relationship created between them? it's preferred to share us your dummy pbix that will be easier for us to provide the advice.

[Anonymous]

thanks for the response  @v-diye-msft . the table PU_Operations DIM_Date is a calendar type table and is correctly connected to the "TABLE" by 2 date fields. I'd be happy to share a dummy model, but am not sure how to go about this on this forum? 

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

If you just wanna create the YTD based on the fact table, no need to add the calendar table, take a try of: 

Add the calculated column first: 

Year = YEAR(‘TABLE’[ProcDate])

then add the measure: 

Measure = CALCULATE(

sum(‘TABLE’[ORDER_QUANTITY]);

    Filter(

        all(‘TABLE’);

            ‘TABLE’[ProcDate] <= MAX(‘TABLE’[ProcDate]));

    VALUES‘TABLE’[Year])

              )

Let me know if it works, or else plz upload the dummy pbix to OneDrive/SharePoint/Dropbox and share me the link.

[Anonymous]

Hi @v-diye-msft , 

thanks again for that. It indeed works, yet my issue was that when I added a slicer with selection criteria, by YTD results did not follow the selections. I.e. the overall totals kept being retained. Elsewhere on the web I came across a solution for this, in replacing "All" with "Allselected" in "All(TABLE[]). 

It doesn't make things faster though (it takes a minute or so to come up with the filtered values) as I appear to have a large volume of data to plough through. Not sure if there's a way to accellerate the calculations. For now, I'm OK with the result. 

thanks for the help!