Calculate a metric using dynamic filters for an interactive user experience

[Anonymous]

Following scenario:

I am calculating a performance indicator using the Calculated Metric below:

Last full week vs 2 weeks ago =

VAR __BASELINE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] IN { "06 | 2 Weeks ago" }

    )

VAR __MEASURE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] IN { "05 | Last Full Week" }

    )

RETURN

    IF(

        NOT ISBLANK(__MEASURE_VALUE),

        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

This is working just fine. The filter value applied to Date_and_Periods'[Period] is static. 

The Date_and_Periods'[Period] has about 30 different Period values in the table and I obviously want to avoid to create a calculated metic for each possible combination. 

I want to create an experience where the user can select in a slicer two different periods which then will be used in the calculation. 

Basically something like:

User picked Periods result =

VAR __BASELINE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] IN USER SELECTED VALUE FROM SLICER 1

    )

VAR __MEASURE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] IN USER SELECTED VALUE FROM SLICER 2

    )

RETURN

    IF(

        NOT ISBLANK(__MEASURE_VALUE),

        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

 I am not sure how I can accomplish this. Any idea would be much appreciated. 

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[DataZoe]

Hi @Anonymous ,

I think you could accomplish this by creating two additional calculated tables, and not creating a relationship to either of them with the main dataset with all the periods for Slicer1 and Slicer2:

Slicer1 = summarize('Date_and_Periods','Date_and_Periods'[Period])

Slicer2 = summarize('Date_and_Periods','Date_and_Periods'[Period])

The user could pick the two periods they want to use from those. 

The calculated metric would then be changed to:

Last full week vs 2 weeks ago =

VAR __BASELINE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] = SELECTEDVALUE(Slicer1[Period])

    )

VAR __MEASURE_VALUE = CALCULATE(

        [Chart-Measure],

        'Date_and_Periods'[Period] = SELECTEDVALUE(Slicer2[Period])

    )

RETURN

    IF(

        NOT ISBLANK(__MEASURE_VALUE),

        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

[Anonymous]

Thank you @DataZoe 

I have tried your solution. However, I do get the following error message for the calculated metric: 

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I am not sure, but it almost seems like CALCUALTE has some limitations with dymanic Filter Values. 

[DataZoe · Accepted solution]

@Anonymous you can get around this by using filter()

Last full week vs 2 weeks ago =

VAR __BASELINE_VALUE = CALCULATE(

        [Chart-Measure],

        FILTER('Date_and_Periods','Date_and_Periods'[Period] = SELECTEDVALUE(Slicer1[Period]))

    )

VAR __MEASURE_VALUE = CALCULATE(

        [Chart-Measure],

        FILTER('Date_and_Periods','Date_and_Periods'[Period] = SELECTEDVALUE(Slicer2[Period]))

    )

RETURN

    IF(

        NOT ISBLANK(__MEASURE_VALUE),

        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

[Anonymous]

This works like a charme! Thank you very much @DataZoe