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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.