How to calculate percentage change based on date selection from any type of date slicer ?

[Anonymous]

I have a dashboard that depicts the sales of a product to a client. I have placed a Relative Date Slicer to offer the client the option to see sales data between two dates and a card that displays the percentage change of the sales between 2 dates.

For the percentage change to work with the date selected by the user on the relative date slicer, I have been instructed to write a long code in a DAX measure for it to work accurately. 

Now I have been tasked to include a Between Date Slicer along with the Relative Date Slicer as well. here, The code I wrote for calculating the percentage change calculation based on the dates selected on the Between Date Slicer is a bit different. 

Since I have only one card displaying the percentage change and
both date slicers are on the same page 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 of the 2 date slicers?  

[v-kkf-msft]

Hi @Anonymous ,

How would you like to calculate the percentage of sales data between 2 dates? Is it by month or by day? Could you please share some sample data?

Best Regards,
Winniz

[Anonymous]

For both slicers.
I calculate the % change based on the period difference between the MIN Date and MAX Date.

For example: If I select 1st June 2021 and 9th June 2021 as Min and Max date respectively for Between date slicer . The difference between them is 9 days. So I take 9 days prior to 1st June 2021, i.e the 23 May 2021 till 31st May 2021.


So I calculate the % change of the sales between the 1st June to 9th June AND 23rd May to 31st May. 

Another Example: Take This month , as I am typing this , today is 2nd Sep 2021. So if I select "This Month"
  on the Relative Date Slicer. I will compare the 1st Sep 2021 till 2nd Sep 2021 (Since only the first 2 days of Sep 2021 have passed) to the whole month previous to Sep 2021 i.e. Aug 2021.

As the user has selected "This month" the user wishes to see the % change between the current month with theprevious.

In other words, I am comparing the % change of the sales between the 1st Sep to 2nd Sep AND 1st Aug to 31st Aug. 

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Anonymous]

Hi, @v-kkf-msft 
I went through the solution you had provided, however, while implementing this. I encountered another problem.

Please see if you can provide any solution to this :
https://community.powerbi.com/t5/Desktop/Syncing-2-different-types-of-date-slicers/m-p/2051563#M7671...

I would highly appreciate your response.

[v-kkf-msft]

Hi @Anonymous ,

This is by design, it is not supported yet currently. You have to clear the filter value of this date slicer when you select another date slicer. We also not found an effective solution to meet your requirement, perhaps you can submit the requirement to ideas and add your comments there to make this feature coming sooner: 

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

Try the following formula. (Note that the Dates table should include all dates of the current month, i.e. 2021-9-1 to 2021-9-31)

Measure = 
var MaxDate = MAX(Dates[Date])
var MinDate = MIN(Dates[Date])
var Diff = DATEDIFF( MinDate, MaxDate, DAY) + 1
var Pre_Sales1 = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALL('Table'),
            'Table'[Date] <= MaxDate-Diff && 'Table'[Date] >= MinDate-Diff
        )
    )
var Pre_Sales2 = 
    CALCULATE(
        SUM('Table'[Sales]),
        PREVIOUSMONTH(Dates[Date])
    )
var Current_Sales = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            'Table',
            'Table'[Date] <= TODAY()
        )
    )
return 
    IF(
        Diff = DAY( EOMONTH( MaxDate, 0 ) ),
        1 - Current_Sales / Pre_Sales2,
        1 - Current_Sales / Pre_Sales1
    )

       

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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