Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My goal is to create a table similar to the visualization matrix below. This works great except when the 3 month period straddles 2 differenct years. (December, January, February). I've tried to recreate this with the PreviousMonths function, but still have the same issue and have the additional problem of having the previous month next to the current month and not separated as below. Do I need to create separate measures for each of current month, current month minus 1 month, minus 2 months etc.? and how? I'm new to DAX Time Intelligence functions and with a lot of help from past postings have created a Dates table and the Prior Month Sales, but now am stuck.
2015 | 2015 Total | 2016 | 2016 Total | Grand Total | |||||
February | March | April | February | March | April | ||||
Customer A | 93 | 134 | 139 | 366 | 88 | 78 | 82 | 248 | 614 |
Customer B | 52 | 55 | 94 | 202 | 65 | 53 | 93 | 211 | 413 |
Customer C | 138 | 113 | 14 | 265 | 265 | ||||
TOTAL | 283 | 302 | 247 | 832 | 153 | 131 | 175 | 459 | 1,291 |
I assume you have fact data per Customer per Month in the fact table. If you just want to display a matrix, you just need to have month group on Year in Column Group of the matrix, then use a slicer to filter expected 3 months.
If you want to calculate the PreviousMonth, Current Month - 2 month Total. You can just use PARALLELPERIOD() in your calculation.
Measure = CALCULATE(SUM('Table'[Amount]),PARALLELPERIOD('Table'[Date],-1,MONTH))
Regards,
Hi @Ria
What about creating a calculated measure with the following below:
YOY = CALCULATE([Measure],SAMEPERIODLASTYEAR('Date'[Calendar Date']))
What this will do, is based on which ever date period you put into your table, it will go to the same period of the previous years and subsequent years.
One thing to note is that you MUST put in the Date columns from your Date table for this to work as expected.
And then put in this measure above into your data as shown below?
This gives me the information I am looking for but is it possible to change the presentation?
Currently:
MonthInCalendar Dec 16 Jan 17 Feb 17 Total
Customer PR YR SALES PR YR SALES PR YR SALES PR YR SALES
A 82 96 85 133 87 113 254 342
(Rows: Customer. Columns: MonthInCalendar. Values: PR YR, Sales.)
___________________________________________________________________________________________________________________________________________
Requested Presentation: Dec 15 Jan 16 Feb 16 Total Dec 16 Jan 17 Feb 17 Total
Customer A 82 85 87 254 96 133 113 342
No, it's not possible have lengend group on measures, you can only have measures group on legend.
Regards,
This gives me the information I am looking for, but if possible is there a way to change the presentation.
Currently:
MonthInCalendar
Can you please post a sample of your data so we can help creating the measures.
It looks doable. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |