Forum Discussion
How to Create Measure that References Two Date Slicers to Produce Percent Change
Hi Anonymous ,
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.
Hi Anonymous ,
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.
This worked - it took me a minute to realize how I would apply it in my file, but it does work!