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
Anonymous
Not applicable

MTD and MTD Last Year in calendar table mark based on date

Hello Folks,

Just I am marking MTD, MTD Last Year and others in Calendar table , using below DAX  and getting correct result but in Last Year MTD calculations giving me full month .

For example like Today 7th 2021 so MTD would be 1-7th Feb of 2021

                     but for Last Year MTD - It should 1-7th Feb2020

Current_Month_LYSM = SWITCH(Dates[Month_Diff],0,"CM",1,"LM",12,"LYSM","Other Months")

 

I dont have to use any measure like TotalMTD(sum(sales))) like this.

I have to idicate based on Dates.

 

Thanks

 

1 REPLY 1
lbendlin
Super User
Super User

That is by design. You need to add a column in your Dates table that flags all the days that are one year older or more than today (or one month older etc depending on what you compare), and then you need to add that flag as a filter in the calculation

 

Something like

 

Previous Year To Date = 

calculate(something something, SAMEPERIODLASTYEAR(sales[date]),dates[isinpreviouspast]=TRUE())

 

Note that I said column, so this assumes that you are refreshing your dataset daily.

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.

Top Solution Authors