Using Slicer in Measures

[Anonymous]

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

[V-lianl-msft · Accepted solution]

It is recommended that you create a disconnected date table as the reference date slicer.

If the problem persists, please share some sample data and the results you expect.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[Anonymous]

Hi @amitchandak 

I have used two date tables as you have mentioned

Order Date Comparison = 'Lian CH$Sales Invoice Line'[Order Date]

Order Date Comparison = 'Lian CH$Sales Invoice Line'[Order Date]

They are been used in the slicers and in the measures.

However, I still get the same error

Not sure what I am doing wrong.

kind regards

Jonas

[amitchandak]

@Anonymous , are the comparison and reference dates(Slicers) coming from two different date tables and one of them is an independent date table?

like the example in this video

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg