cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Date Slicer and Compare Date Slicer

Hello,

I currently have two date tables. One is .Date and the other is .Compare Date. Each are created by the function:

CALENDAR(DATE(2022,01,01),DATE(2025,12,31))

I currently have the .Date table being filtered by a relative date slicer (this month, next month, this year, next year, etc).

I would like to create a slicer that when "last month" (1/1/24 - 1/31/24) is selected on the slicer that is filtering the .Date table, I can select:

• "Previous Period" that would filter the .Compare Date table to 12/1/23 - 12/31/23
• "Previous Year" that would filter the .Compare Date table to 1/1/23 - 1/31/24
• "None" that would return blank

I have created the following DAX measures to get the selected dates I need, but I can't quite figure out how to put this into a slicer or get this to filter the .Compare Date table instead of the .Date table:

Previous Period MAX = (MAX('.Date'[Date]) - 1) - [Selected Dates]
Previous Period MIN = (MIN('.Date'[Date]) - 1) - [Selected Dates]

Previous Year MAX = (MAX('.Date'[Date]) -365)
Previous Year MIN = (MIN('.Date'[Date]) -365)

Selected Dates = DATEDIFF(MIN('.Date'[Date]), MAX('.Date'[Date]), DAY)

Thanks,
4 REPLIES 4
Super User

from what i understand, and correct me if im wrong,  you need to have a slicer with the following values :

and base on the selection , it will change the measure in the visual respecting the selecteed value .

if this is what youu want ,

you can use field paramters feature to achieve it .

let me know if this helps .

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! 🤠

Super User

HI @jn123 ,

If i understand correctly, i would still believe that if using measure and using proper DAX calculation which considers previous period, values from compare table can be calculated. I dont see use case for second slicer. if you do not want to allow user to select anything from the sliver

But neverthless if you want to have possibility to add it on slicer, we can check with experts in the community

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!!

Proud to be a Super User!

Super User

Hi @jn123 ,

Unfortunately i am not aware how to create dynamic slicer for compare dates.

I'm having difficulty understanding the specific purpose of providing a Compare Date slicer to the user.

I was wondering if it would be possible to achieve the desired outcome by creating direct measures that calculate for the previous period or year?

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!!

Proud to be a Super User!

Frequent Visitor

@sayaliredij The goal is for the end user to be able to see for example, total sales for "this" month (filtered by the .Date table) and then be able to see the percent difference of the sales "this month" vs the "previous month" or the same period in the previous year. The percent difference calculation is based on the dates in the .Compare table so I need to be able to filter the .Compare dates based on what is selected on the .Date table.

Thanks,

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors