cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Month Over Month and Year over Year with Multiple Metrics

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.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Community Support

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

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

Helper I

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors