The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
@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.
@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?
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |