Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Problem with LY MTD Calculations

Hi All,

 

I am using the following formula to calcuate LY MTD

LY MTD Inv Sales =  CALCULATE( [Total Inv Sales], DATESMTD( SAMEPERIODLASTYEAR(DimDate[Date]) ) )
 
Assuming today is 25th Mar 2020
The problem is this MTD calculation is also taking sales of dates greatar than 25th March 2019.
How can i exclude these days from my calculation.
 
Will appreciate any help !
Thx
Fahad
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous 

    Try measures as below:

     

    LY MTD Inv Sales = CALCULATE(SUM('Table'[Value]), FILTER(DATESMTD( SAMEPERIODLASTYEAR('Date'[Date]) ),MONTH([Date] )<MONTH(TODAY()) || (MONTH([Date] )=MONTH(TODAY())&&day([Date] )<=day(TODAY()))))

     

    Here is a demo.

    Pbix attached

     

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

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can try like

    last year MTD Sales = CALCULATE(SUM([Total Inv Sales]),DATESMTD(dateadd('DateDim'[Date],-12,MONTH)))

     

    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))))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for responding Amit.

      I am afraid i cant follow the your answer.

      Isnt there an easy way of filtering out the unwanted  days of LY MTD (26-31st march 2019) by may be flagging dates in calendar table ?

      Thx

      Fahad

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        6 years ago

        Hi , Anonymous 

        Try measures as below:

         

        LY MTD Inv Sales = CALCULATE(SUM('Table'[Value]), FILTER(DATESMTD( SAMEPERIODLASTYEAR('Date'[Date]) ),MONTH([Date] )<MONTH(TODAY()) || (MONTH([Date] )=MONTH(TODAY())&&day([Date] )<=day(TODAY()))))

         

        Here is a demo.

        Pbix attached

         

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