Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need to create a matrix tied to a slicer that shows the month of selection + the prior month. For example, say I choose April 2022. I want to show stats for April as well as March + show the difference (delta) between the two. is this possible, or is there a better solution (something similar to what was being done in Excel, screenshot below)?
So far I've created a calendar in powerbi and joined my incoming table dates to the dates in the PowerBI calendar. I created 2 measures (below), 1 for the current month, another for the prior month, but when I drop both measures into the matrix tied to the monthly slicer, both produce the exact same number?
Thx!!
Thismo_Texas = DIVIDE(SUM(Data[ORS_Num]),(SUM(Data[ORS_Den]))
Lastmonth_Texas = CALCULATE(DIVIDE(SUM(Data[ORS_Num]),(SUM(Data[ORS_Den]))),DATEADD('date'[Date],-1,MONTH))
Thx!!
Solved! Go to Solution.
@Anonymous , My doubt is that month selected is not coming from date table. If that is not the reason.
If this does not help
Can you share a sample pbix after removing sensitive data.
Hi,
See below for the measures used, dropped into a matrix, + the slicer is selecting the month of April. Notice both measures are the same number, even though one is a calculation of 2 months prior, the other is just the sum of data. Why is the slicer treating these both the same? The third screenshot below shows the relationship between my calendar table and my data table. I join on date, one to many relation ship. Calendar was complied as follows:
@Anonymous , My doubt is that month selected is not coming from date table. If that is not the reason.
If this does not help
Can you share a sample pbix after removing sensitive data.
Thx!! Correct, the snapshot is from Excel. I am converting metrics from Excel to PowerBI
Hi,
You are correct that month_selected is not coming from the calendar table. here's the DAX, it is year concatenated with month + a dash between. Will this not work?
Also, I must concat year and month into the slicer
@Anonymous , for time intelligence, to work. Slicer, filter, and visual should use column/s from date table
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
@Anonymous , Your formula should work , only thing is that it will not show month no , Prior month measure will also show against the current month
Make sure the date table is marked as the date table and you are using month year in visual from the date table
refer Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Thx!! This worked. Appreciate the help!!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |