How to create a measure to calculate percentage change based on date range slicer ?

[Tapojoy]

Hi everyone ! @PowerBIDev 
I have a time-series line plot showing values for last 90 days and have a date range slicer, based on which the chart gets displayed.

I need to create a new measure that is able to calculate the percentage change based on the date range selected through the slicer.

The time-series line plot is as shown below :

For example, if the date range in the slicer is selected as :  19/08/2020 - 17/10/2020, 
the percentage change measure should be calculated as :-
[ ( Value on 17/10/2020 - Value on 19/08/2020 ) / Value on 19/08/2020 ] X 100 %.

I tried a DAX code, but getting the following error :

Please suggest any solutions or redirect to any other link, if already answered.

Thanks in advance ! 😄

[Tapojoy]

Thanks a lot ! It works now.

[OwenAuger · Accepted solution]

Hi @Tapojoy 

The reason for the error is that the Filter arguments provided to CALCULATE must either be tables or appropriate boolean expressions that refer to a single column.

Since MIN and MAX functions are scalar functions, they don't return tables, so startdate and enddate aren't valid filter arguments.

You can fix this issue by by using FIRSTDATE and LASTDATE, which return tables containing a single row/column corresponding to the min/max date. I think the order of arguments in DIVIDE also needs to be switched, and there was an unnecessary CALCULATE:

perc_change =
VAR startdate =
    FIRSTDATE ( daily_count[date] )
VAR enddate =
    LASTDATE ( daily_count[date] )
RETURN
    DIVIDE (
        CALCULATE ( [CumCount], enddate ),
        CALCULATE ( [CumCount], startdate )
    ) - 1

Also, I would advise creating a separate Date dimension table to use for all date filtering (various articles on this online).

Regards,

Owen

[aausten22]

For this, how would it work if I wanted the Start Date to be the SELECTEDVALUE and the end date being the previous month based on the Start Date?

ie: Start Date = Jun 23 so End Date = May 23

[Anonymous]

Greetings ,
I have a slightly different problem.

I have one card displaying the percentage change based on date selection and
2 date slicers (Between and Relative Date) on the same page with the card and
I have two different algorithms for % change for dates selected by Between Date Slicer and Relative Date Slicer separately.

Is there a way to detect what type of date slicer is selected, so this way I can switch to the right algorithm based on the date slicer selection?
If not then,
How to calculate % Change based on dates from any type of date slicer?