Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
niakait
New Member

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

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. 

Microsoft Fabric Question.PNG

1 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())

vjunyantmsft_0-1710913408437.png
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.

vjunyantmsft_1-1710913781010.png

vjunyantmsft_2-1710913794586.png

 

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.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-junyant-msft
Community Support
Community Support

Hi @niakait ,

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

vjunyantmsft_0-1710900645201.png

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

vjunyantmsft_1-1710900698460.png

vjunyantmsft_2-1710900712302.png

vjunyantmsft_3-1710900723549.png

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:

vjunyantmsft_4-1710900845614.png
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.

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. 

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())

vjunyantmsft_0-1710913408437.png
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.

vjunyantmsft_1-1710913781010.png

vjunyantmsft_2-1710913794586.png

 

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.

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

Daniel29195
Super User
Super User

@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! 🙏

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.