cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shankarshiva70
Helper I
Helper I

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 III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors