cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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, 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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

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.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Anonymous
Not applicable

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

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? 

Highlighted
Community Support
Community Support

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

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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

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

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (722)