cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnmc Frequent Visitor
Frequent Visitor

Using Slicer Value to Limit Data

 

I have a slicer dropdown which is a list of weeks from a table

 

The user selects a week and aggregations of that data (e.g. sales) are shown in various cards on screen

 

I'd also like to have a chart which displays all the history of sales by week up to the week selection

 

Bit stuck on how to do the chart bit. Can anyone help please?

 

 

3 REPLIES 3
Super User
Super User

Re: Using Slicer Value to Limit Data

You would need to create a measure that does an ALL and then FILTERs that down based upon the date being less than the chosen date. Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Using Slicer Value to Limit Data

Hi @johnmc,

 

Assuming that you have a weeknum column, you could create a measure like below.

 

Measure =
IF (
    ISFILTERED ( 'table'[weeknum] ),
    CALCULATE (
        SUM ( 'table'[Sales] ),
        FILTER (
            'table',
            WEEKNUM ( 'table'[Date] ) = WEEKNUM ( MAX ( 'table'[Date] ) )
        )
    ),
    BLANK ()
)

Then when you select the weeknum in a slicer, you will get the weekly total sales.

 

weekly total.PNG

 

If you still need help, please share some data sample and your desired output so that we could help further investigate on it?

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
johnmc Frequent Visitor
Frequent Visitor

Re: Using Slicer Value to Limit Data

Hi - thanks for the replies and the direction so far. I've posted screenshots of my process

 

I have a calendar table and data table which are linked.

I then create a drop down slicer based on the weekend_id in calendar

I then create a couple of measures - TY Sales and TY Sales 4 Wk Rolling

 

I then add a card and a table to the report.

Card = TY Sales 4 Wk Rolling

Table = weekend_id from calendar and the x2 created sales measures

 

The card is displaying the correct value

How would I go about getting the table to show a row by row history of say 52 weeks up to the selection date?

 

In methods so far I can only get one or the other to happen

 

Capture1.PNGCalendar tableCapture2.PNGData tableCapture3.PNGCalendar and Data have an active relationshipCapture4.PNGAdd a slicer to the report from the calendar table (weekend_id)Capture5.PNGCreate a summed sales measureCapture6.PNGCreate a rolling summed sales measure

 

Capture7.PNGAdded a card and table to the report. I like the card value, but want the table to show 52 weeks of row by row history up to the selected date if possible