Finding Month-to-Month Variances From Two PowerBI Matrix

[DeltaZero]

Hi, new here. Need some help with Power BI, thanks in advance and for such an awesome community.

I have a dataset in excel which have five different variables namely the Date, Area, Type, Region and Amounts, and I would like to present my data in Power BI similar to this format.

File: https://we.tl/t-lwkxPCEZas

As you can see from the image from excel, my two pivots on the left present the data such that they are of different dates i.e. 31/12/2022 and 31/01/2023. How do I create two more matrices in Power BI to show Month-on-Month Variances and % Variances?

Thank you!

[v-rzhou-msft · Accepted solution]

Hi @DeltaZero ,

Here I suggest you to create measures to achieve your goal.

Current Amount = 
CALCULATE(SUM('Table'[Amounts]))

Previous Amount = 
VAR _LASTMONTH = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
RETURN
CALCULATE(SUM('Table'[Amounts]),FILTER(ALLEXCEPT('Table','Table'[Area],'Table'[Region],'Table'[Type]),'Table'[Date] = _LASTMONTH))

Month-on-Month Variances = 
[Previous Amount] - [Current Amount]

% Variances = 
DIVIDE([Month-on-Month Variances],[Current Amount])

Result is as below.

You can show results in four matrixs and you also show them in only one matrix.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rzhou-msft · Accepted solution]

Hi @DeltaZero ,

Here I suggest you to create measures to achieve your goal.

Current Amount = 
CALCULATE(SUM('Table'[Amounts]))

Previous Amount = 
VAR _LASTMONTH = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
RETURN
CALCULATE(SUM('Table'[Amounts]),FILTER(ALLEXCEPT('Table','Table'[Area],'Table'[Region],'Table'[Type]),'Table'[Date] = _LASTMONTH))

Month-on-Month Variances = 
[Previous Amount] - [Current Amount]

% Variances = 
DIVIDE([Month-on-Month Variances],[Current Amount])

Result is as below.

You can show results in four matrixs and you also show them in only one matrix.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeltaZero]

Oh wow! This is awesome, I never expect that they can be shown in just 1 matrix - the latter is so much better because I will need to put charts, and that would save me a lot of space. Just wondering - going forward, I will have date: 20230228, 20230331, 20230430...

Does the DAX formulae work if let's say I am on 20230430, it will automatically be compared against 20230331?

Also, would you be able to provide me with this PBI file instead? 🙂

Thanks a lot!

[v-rzhou-msft · Accepted solution]

Hi @DeltaZero ,

This dax formula will work if there is only one date for each month to derermind the month. Just like your sample 2022/12/31 and 2023/01/01, If you select 2023/01/01, this measure will compare it with 2022/12/31.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.