Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slavisha84
Helper I
Helper I

How to create MTD column that summarize data up to certain week?

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:

MTDdata = ADDCOLUMNS(SUMMARIZE(FiscalCalendar,FiscalCalendar[FiscalMonth]),
"LastYearRevenue",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = YEAR(TODAY())-1),
"ThisYearRevenue",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = YEAR(TODAY())))


And then I created calculated columns as:

Variance = IF(MTDdata[ThisYearRevenue]<>0,MTDdata[ThisYearRevenue]-MTDdata[LastYearRevenue])
%YoY = MTDdata[Variance]/MTDdata[LastYearRevenue]

And this gives me the table that looks like this:

 

slavisha84_0-1620247409098.png

 

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


 




3 REPLIES 3
amitchandak
Super User
Super User

@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:

slavisha84_0-1620318649874.png


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. 

slavisha84_0-1620343063223.png

 

WTDdata = ADDCOLUMNS(SUMMARIZE(FILTER(ALL(FiscalCalendar),FiscalCalendar[FiscalWeek]<[CurrentFW]),FiscalCalendar[FiscalWeek]),
"LastYearRevenue",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = YEAR(TODAY())-1),
"ThisYearRevenue",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = YEAR(TODAY())))
 Where CurrentFW is a measure that calculates the current fiscal week which I use to narrow down the data. 

Now, with this logic, where i narrow down the dataset by creating WTDdata with up to 17th fiscal week, is there i way i could do the same within my original MTDdata table without having to create WTDdata?
 




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.