Period slicer to also calculate YTD Slicer

[Anonymous]

Hi, 

I have a date defintion table on with dates which correspond to the financial year and period they fall in.

Data Table:

Date

GBP Value

Period Definition table:

Date

Financial Year

Period

(Key is by date)

I've been using this fine to slice reports by financial year and period.

However on some reports I have to have two slicers; one is a single select period slicer so visualisations display data for the selected period and a second slicer which is a range slicer so it displays year start to selected period data. However having the users change two slicers isn't ideal...

What I'm trying to do it make it so when a user selects a period in the single select dropdown, rather than needing to change the second filter my visualisations use some form of a <= measure on the period slicer. 

Above is what I'm trying to achieve without the need for the range slicer.

(I do have other columns and slicers that come into play but left them out for simplicity)

Any thoughts?

Thanks!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I'm not sure I got you. You want to change the period slicer and show the data which is less than the selected period. Do I understand correctly? If so, you could reference my steps.

• Create a new table with the period column to as a slicer.  There is no relationships among the tables.

Table = SUMMARIZECOLUMNS('Period Definition'[Period])

• Create a measure

Measure = 
var sele = SELECTEDVALUE('Period Slicer'[Period])
var result = CALCULATE(SUM(Data[GBP Values]),FILTER('Period Definition','Period Definition'[Period] <= sele))
return
IF(ISFILTERED('Period Slicer'[Period]),result, SUM(Data[GBP Values]))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xuding-msft 

Thanks, your answer got me 99% of the way there. 

I just needed a second measure replacing 'Period Definition'[Period] <= sele with 'Period Definition'[Period] = sele then a table filter for the second table where Measure 2 IS NOT BLANK got the desired result.