How to Create Measure that References Two Date Slicers to Produce Percent Change

[niakait]

I will try my best to explain the output I am looking for. 

I have cards created that use a "Before" date range slicer and others that use an "After" date range slicer. 

Unfortunately, we are unable to create a difference between two filtered cards.

As such, I need to:

1. Create a measure that performs the calculation of each card (they are the average of the values in the table between the selected date range)

2. Create a measure that calcualtes the percent change between the before and after value

I just need guidance on the DAX formulas I should use to accomplish this. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-junyant-msft]

Hi @niakait ,

I create an example for you.
Here is my sample data:

And I add two Date tables for the slicer without any relationships:

I use these DAXs to calculate "Before" and "After":

Before = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= MIN('Before'[Before - Date Range]) && 'Table'[Date] <= MAX('Before'[Before - Date Range])
    )
)

After = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= MIN('After'[After - Date Range]) && 'Table'[Date] <= MAX('After'[After - Date Range])
    )
)

And use this DAX to calculate the difference:

percent = DIVIDE('Table'[After], 'Table'[Before]) - 1

The final output is as below:

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

[niakait]

Thank you, however the two date ranges are dynamic and creating a table for each every time I wish to populate the report is not the intended goal. 

What I want is for a team member to be able to change the date ranges and then the percent change is populated, without them doing anything else. 

It would require me to reference the slicer start and end dates in the "Before" slicer and the same in the "After" slicer. That is where I am held up at the moment. 

[v-junyant-msft · Accepted solution]

Hi @niakait ,

About this "however the two date ranges are dynamic and creating a table for each every time I wish to populate the report is not the intended goal. " You can use DAX to create the Table. For example, the date range you want is from 2023.3.1 to today, then you can use this DAX:

Table 2 = CALENDAR(DATE(2023, 3, 1), TODAY())

This table will change the date range as the day's date changes.
Or you can create a new table with a larger range of dates to include all the date ranges you may need to use so that you don't need to create a new date table each time.

And if you don't create new tables and just use the column in the original table, it will apply filtering to all visual objects that use data from that table, which can be cumbersome when using DAX for calculations. It is possible that you will also need to set disabled filtering on some visual objects, not as convenient as creating a new unrelated table to create the slicer.

And about this "What I want is for a team member to be able to change the date ranges and then the percent change is populated, without them doing anything else". I have achieved this in the pbix.

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

[niakait]

This worked - it took me a minute to realize how I would apply it in my file, but it does work!

[Daniel29195]

@niakait 

this article would help. 

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🙏