Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create MTD table which will summarize the data based on the month and then produce Variances between last year and this year with a % difference.
I have made this table using this DAX formula:
And then I created calculated columns as:
This is what I need however I am having an issue with the current month. Since the current month is still in progress and I am missing one week of data, by comparison to last year's same month is not valid since the data for last year's same month is for a complete month.
For example, last year April and this year April have 5 weeks based on the fiscal calendar I am using. I have the full 5 weeks of April for 2020 but only 4 for 2021. I need to produce the MTD summary for last year to only include the data up to the same point that I have the data for this year.
How do I do that?
Here is the location for sample data and .pbix file:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=C7ObDZ
@slavisha84 , Try like
MTDdata = ADDCOLUMNS(SUMMARIZE(FiscalCalendar,FiscalCalendar[FiscalMonth],FiscalCalendar[FiscalYear], "_1", sum(RevenueByDate[Revenue])),
"LastYearRevenue",sumx(filter(FiscalCalendar,FiscalCalendar[FiscalYear] =earlier(FiscalCalendar[FiscalYear])),[_1])
"ThisYearRevenue",sumx(filter(FiscalCalendar,FiscalCalendar[FiscalYear] =earlier(FiscalCalendar[FiscalYear]) -1),[_1])
)
I am not sure about the need for a new table, You can always deal with this in UI, using time intelligence
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
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
@amitchandak Hi Amit, thank you for your reply. I do need a table because I will be calculating lots of other things from it.
The table i get by using your solution is not what i was looking for:
It still adding the data for other months. If i am in let say 4 week of April and april has 5 weeks, i should see montly data for each month not further than 4th week of april for each year.
So as a workaround I created WTDdata with a weekly summary up to a current week and then I did MTDdata with a monthly summary off of WTDdata. That way I limit MTDdata only to columns I have in WTDdata.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |