Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

MEASURE: LMTD

Hi!   I want to create a measure without using a Datetable. For MTD im using: Month to Date = TOTALMTD(SUM(fLeads[LeadCount]), fLeads[DATE_KEY_LEAD]) For Last Month im using: Last Mont...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

    Try this measure for Previous Month MTD.

     

    Prev MTD = CALCULATE(SUM(fLeads[LeadCount]), PREVIOUSMONTH(DATESMTD(fLeads[DATE_KEY_LEAD])))

     

     

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
      

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , You have to use date table.

 

refer these examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
6 years ago
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

This is giving me result for the WHOLE last month and not LMTD as I want to. 

 

Today is 27 July.

I want my LMTD to return result for 27 June.

  • newbie_pro's avatar
    newbie_pro
    Regular Visitor
    3 years ago

    Do you have the solution?
    please share if you do..