cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Can you perform a calculation or filter a visual based off a user selected Date Page filter?

Hi

I'm looking to create a variance measurement. Simply put I want to calculate the variance between applications received compared to the same month last year.

However, I wont know the month until the user selects the page filter and selects the Year and Month. So is there a way I can create a calculation or a visual filter on a group of filters that is set to the prior month and year of whatever Year and Month the user selects as the page visual.

There will be a range of this comparison metrics. Wondering if someone has worked out a workflow to acheive this.

Thanks,

1 ACCEPTED SOLUTION
Super User

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the Calendar Table
3. To any visual/filter/slicer, drag Year and Month from the Calendar Table and make a selection of a year/Month
4. Write these measures

Total = sum(Data[applications])

Total in SPLY = calculate([Total],sameperiodlastyear(calendar[date]))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the Calendar Table
3. To any visual/filter/slicer, drag Year and Month from the Calendar Table and make a selection of a year/Month
4. Write these measures

Total = sum(Data[applications])

Total in SPLY = calculate([Total],sameperiodlastyear(calendar[date]))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors