Forum Discussion
Month to Month Variance (based on ITD record data)
Hi everyone,
think i tried most of the month to month variance solution but somehow it doest work particularly because my data with date are mapped in a calender hierarchy.
backgroud problem :
- i have project data where the number are recorded as Inception to date Reveneu (from project start to current period)
- i wanted to get the current month revenue so logically i need to minus current recorded data minus last month recorded data.
here is my data stucture and relationship where the date from calender table relates to my data table.
What i need is to show variance of RR this month vs RR last month which technically is current month RR.
hi Anonymous
This is that you do not drag Year column into the visual, just like this:
Because that if you don't put year in the visual, it will calculate the max year data for "Date" table, in your case, it is 2025, so you just add a year column as above.
Regards,
Lin
12 Replies
- amitchandak
Super User
Anonymous , In case you have date in your table and Date calendar , you can use datesMTD
refer :https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Example
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 MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month)) diff = [MTD Sales]-[last MTD Sales] diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi,
thanks for the respond amitchandak
however i assume the example given is using sum formula whereby its adding all current month data and sum it first before calculatiing the difference where my data are already as a sum. how can i change it?
- amitchandak
Anonymous , if you have one row and it is sum (Maybe separate row for other group bys like the brand product, item, etc), sum should work.
As long as the same number is not repeating you can sum. If there are repeating can you share example data?
Can you share sample data and sample output in a table format?
