Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using Slicer in Measures

Hi All

 

I have some issues with a current user requirement.

 

I am trying to create a page where user can check if some customers have sudden revenue decrease.

 

To do so I want to use multiple slicers

 

1 Slicer to define the reference time Period

1 Slicer to define the comparison time period

1 Slicer to define the threshold for revenue decline

 

e.g. I want to check customers that have more than 80% revenue decline in the time period 1.6.2021 - 30.60.2021 compared to my reference time period of 1.1.2020 - 31.12.2020.

So my calculation would be "Losts Customers  =  (turnover comparison time period /30) / (turnover reference time period / 365) <= (1-0.8)

 

The formula itself is not the problem. I'm currently have some issues to extract the dates from the slicer. I am using allselected but I am not sure if that is the right approach.

 

I am using the dax formulas below to extract start / end date of the reference time period:

StartDate Reference = CALCULATE(MIN('Lian CH$Sales Invoice Line'[Order Date]),ALLSELECTED( 'Lian CH$Sales Invoice Line'[Order Date]))

 

EndDate Reference = CALCULATE(MAX('Lian CH$Sales Invoice Line'[Order Date]),ALLSELECTED( 'Lian CH$Sales Invoice Line'[Order Date]))

 

Turnover ReferencePeriod = CALCULATE (
    SUM ( 'Lian CH$Sales Invoice Line'[AmoutNoVat] ),
    FILTER('Lian CH$Sales Invoice Line','Lian CH$Sales Invoice Line'[Order Date] <= [EndDate Reference] && 'Lian CH$Sales Invoice Line'[Order Date] >= [StartDate Reference])
)

 

It  is working fine if I just use the reference time period, however, when adding a second slicer, it is no longer working.

 

Is there any way to extract start End Date of a single slicer and not extracting min / max date of the selected date range?

 

Current issue:

 

 

 

Thanks in Advance!

 

kind regards

Jonas

3 Replies