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

[slavisha84]

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:

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

[amitchandak]

@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

[slavisha84]

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

[slavisha84]

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. 

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?