cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_CJ
Frequent Visitor

LMTD is correct in Desktop, but not in Web

I have a visual which shows MTD sales units and Last MTD units.  The visual in desktop is working correctly, however once it's uploaded to My workspace, LMTD reverts to sales units for the entire month.  
 
I'm self taught and still relatively new to DAX, so a lot of what I've used is from these forums and trial and error.  
 
Last MTD Units = CALCULATE([Units],DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date]<TODAY()), -1,MONTH))
 
Any ideas? Thanks in advance!
 
 
1 ACCEPTED SOLUTION

@BI_CJ , Mybad, you need to use calendar in place of date Table

 

LMTD Units forced=
var _max1 = maxx(allselected(Calendar), Calendar[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
if(max('Calendar'[Date])<=_max, CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH)), blank())
//or

 

 

or

 

LMTD Units forced=
var _max1 = maxx(allselected(Calendar), Calendar[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
 CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH) ,'Calendar'[Date]<=_max)
//or

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@BI_CJ , Try like

 

Last MTD Units = CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH))

 

or

 

LMTD Units forced=
var _max1 = maxx(allselected(Date), Date[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
if(max('Date'[Date])<=_max, CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH)), blank())
//or

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

I tried the first option previously and it shows full month in desktop.  

 

Second option gives me this error: Can not find name 'Date'

 

The syntax for '[Date]' is incorrect. (DAX(var _max1 = maxx(allselected(Date), Date[Date])var _max = date(year(_max1), Month(_max1)-1, day(_max1))returnif(max('Date'[Date])<=_max, CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH)), blank())//or)).

@BI_CJ , Mybad, you need to use calendar in place of date Table

 

LMTD Units forced=
var _max1 = maxx(allselected(Calendar), Calendar[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
if(max('Calendar'[Date])<=_max, CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH)), blank())
//or

 

 

or

 

LMTD Units forced=
var _max1 = maxx(allselected(Calendar), Calendar[Date])
var _max = date(year(_max1), Month(_max1)-1, day(_max1))
return
 CALCULATE([Units],DATEADD(DATESMTD('Calendar'[Date]), -1,MONTH) ,'Calendar'[Date]<=_max)
//or

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Thank you! That works.  One more question.  How would I exclude today's date in the LMTD calculation since there is a 1 day lag in my data?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.