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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Filter data with slicers by cumulative periods. Data contains data.mm.yyyy dates | Power Pivot

I have a table with dates (dd.mm.yyyy) and transactional data (sales, discounts, hour rates etc). I have also a separate calendar table. I want to build Power Pivot charts with slicer there user selects “Last month” or "Last 3 Months" or “YTD” and the transactional data in the chart switches based on this selection filtering only data for relevant months. I’ve tried to build via SWITCH but didn’t succeed. what I want is when user selects "YTD" at the slicer than all the metrics would use only data for periods attributed to YTD. 
Second point is that in those charts I also want to show growth vs same cumulative period last year based on user selection in slicer. When user selects eg "YTD" chart shows growth YTD vs YTD previous year; if user selects "Last month" the calculation of growth % is based only on last month vs previous year same month.
I believe this should be solved via SAMEPERIODLASTYEAR but I am confused if I have to create measures for each metric (sales, discounts, hour rates etc)? And how to link it with the slicer selection of periods?

 

Sample data file with destination view 

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , you should be able to do with date table and time intelligence there too, as long as dax is supported

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak Thank you so much for the consideration. Unfortunately suggested solution is a bit different from what is needed.

 

What is suggested makes separate MAT metric for each KPI (MAT sales, MAT discount etc). And those won't be linked to MAT slicer.


What I want is when user selects "MAT" at the slicer than all the metrics would use only data for periods attributed to MAT. The slicer should filter whole table leaving only appropriate periods and then calculating subtotals for all measures.

 

I have a sample data with the desired output at the "Destination Tab". Can you please have a look if you have ideas on how to resolve that?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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