Forum Discussion
Period-to-Period Variance(s) & Percentage(s) Without Hardcoding
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.
12 Replies
- amitchandak
Super User
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=6LUBbvcxtKAThanks 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.
Bump
- Ashish_Mathur
Hi,
I can try. Share the download link of your PBI file.
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:
- sample data.xlsx
- 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!
