Period-to-Period Variance(s) & Percentage(s) Without Hardcoding

[Starstorm]

Hi all, I am new here - great to be in this wonderful community.

I need help with showing the variances (in values & percentages) between periods in a matrix table for at least 24 months automatically without any hardcoding.

It should look like the following, with the left hand side for Power Query Data Model, and the right hand side for Power BI.

The DAX formulae should be such that every time I load a new subsequent month, it will automatically compute the variances by having the understanding that the latest MAX(month) is the most recent month, which will be compared against [MAX(Month)-1], and subsequently comparing [MAX(Month)-1] against [MAX(Month)-2], and [MAX(Month)-2] against [MAX(Month)-3], so on and so forth.

Thank you, and appreciate your support and guidance.

[Starstorm]

Bump

[Ashish_Mathur]

Hi,

I can try.  Share the download link of your PBI file.

[Starstorm]

Thanks for reaching out. Apologies for the delay, here are the files.

Please find the download link here: https://we.tl/t-K3XbSkReCw

There are two files there:

1. sample data.xlsx
2. sample data.pbix

I try to simulate the same process as what I intend to perform. Power Query at Excel first, then Power BI. I randomly generated amounts for assets, liabilities and equities for simplicity.

Thanks for the help!

[Ashish_Mathur]

I do not see a download link there.  Try sharing the download link over Google Drive.

[Starstorm]

Here you go, let me know if you cannot access the .rar file: https://drive.google.com/file/d/1SMs4PoY5dKx-Lbpbz02LkwA1tLyAv6e8/view?usp=drivesdk

Appreciate, and thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Starstorm]

This confirms the MoM variance(s) are correct, but is there a way such that I just want the table to show, let's say February 2021 and March 2021; and it will only show their 2 values, with another table below for their variance?

Thanks again.

[Ashish_Mathur]

Hi,

See the image below

[Starstorm]

Thank you! It does show the variances respectively, and correctly. Is there a simplier way of doing? It looks so complicated. 🙂

EDIT: By the way, are you able to provide me with some colors on how does the "Months to offset by" work?

[Ashish_Mathur]

You are welcome.  I wish i knew of a simpler way to solve this.

[amitchandak]

@Starstorm , you can use the date table and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Starstorm]

Thanks for the prompt response.

I assume the above only works for the last two months i.e. 31-12-2022 and 30-11-2022, which is variance for current and prior months. Would it be possible to have the BI to automatically compute the variance for the rest of the previous months-on-months i.e. 30-11-2022 and 31-10-2022?

My data is with time intelligence.

Thanks again.