Forum Discussion

johnmc's avatar
johnmc
Frequent Visitor
8 years ago

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...
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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.

 

 

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

johnmc's avatar
johnmc
Frequent Visitor
7 years ago

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

 

Calendar tableData tableCalendar and Data have an active relationshipAdd a slicer to the report from the calendar table (weekend_id)Create a summed sales measureCreate a rolling summed sales measure

 

Added 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