Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi
Wonder if anyone can help, im trying to create a table with last month and the previous month and compare the values of multiple metrics, I have mocked up an example of the data and what is needed.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, @Sbudd
In order to display the row fields you want in the matrix, you need to Unpivot three fields in the Power Query.
You can then create 5 measures to display the 5 metrics you want.
Since there is only one date in a month in the data above, it would be better to rename the month column to date when there are more dates in a month. And you can create a calculated column to display the year and month correctly as well as to filter the data.
YYYY-mm = FORMAT('Table (2)'[Month],"YYYY-mm")
Here are 5 measures that can work for you.
LastMonth =
var _lastMonth=CALCULATE(MAX('Table (2)'[Month]),ALLSELECTED('Table (2)'))
return
CALCULATE(
SUM('Table (2)'[Value]),
FILTER(ALLEXCEPT('Table (2)','Table (2)'[Attribute]),EOMONTH('Table (2)'[Month],0)=EOMONTH(_lastMonth,0)))
Previous Month =
var _lastMonth=CALCULATE(MAX('Table (2)'[Month]),ALLSELECTED('Table (2)'))
return
CALCULATE(
SUM('Table (2)'[Value]),
FILTER(ALLEXCEPT('Table (2)','Table (2)'[Attribute]),EOMONTH('Table (2)'[Month],0)=EOMONTH(_lastMonth,-1)))
MOM% =
DIVIDE(
[LastMonth]-[Previous Month],[Previous Month])
LastYear =
var _lastMonth=CALCULATE(MAX('Table (2)'[Month]),ALLSELECTED('Table (2)'))
return
CALCULATE(
SUM('Table (2)'[Value]),
FILTER(ALLEXCEPT('Table (2)','Table (2)'[Attribute]),EOMONTH('Table (2)'[Month],0)=EOMONTH(_lastMonth,-12)))
YOY% =
DIVIDE(
[LastMonth]-[LastYear],[LastYear])
Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
i think you should unpivot your table from
into this table:
you can unpivot your table using the Power Query Editor (Edit Query)
then you can add calculated column to get previous month value:
Previous Month =
var fldAttr = 'Table'[Attribute] // This is the metrics
var fldPrevMonth = calculate( max('Table'[Month]), filter('Table', 'Table'[Month] < EARLIER('Table'[Month]) ) )
var fldValue = sumx( filter('Table', 'Table'[Month] = fldPrevMonth && 'Table'[Attribute] = fldAttr), 'Table'[Value] )
return fldValue
then you can have like this table as a result
Thanks for the speedy help!
Im getting errors, so I feel we're 99% there.
FIrst one was "A Single value for column Attribute in Tablecannot be determined. SO i wrapped in in a MIN which removed that error.
Second error im getting is "EARLIER refers to an earlier row context which doesnt exist"
Any ideas?
i guess you create a new measure rather than a new column (calculated column).
just create a new column like this picture.
you can go to this link https://drive.google.com/file/d/1XGIOPK9jC6SdA-QlOrOqhmCYAtH_WdIw/view?usp=sharing for sample pbix
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |