Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
2 years ago

Calculate MTD,

Hi , I'm very struggling on calculated the data using MTD performance, 
i would like to compared MTD in monthly to see the real performance. 
What i'm having right now is always showing MTD data vs. Whole month last year , which is not right as expected. 

can you share me the right formula ? 
ie. From 01st Jan to 21st Jan 2024 vs.  01st Jan to 21st Jan 2023

 

Thank 

Show and Tell

8 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    hello Chanleakna123 

    you can use this measure : 

     

    Measure 9 =
    CALCULATE(
        [Revenue],
        SAMEPERIODLASTYEAR(DATESMTD('Date Table'[Date]))
    )

     

    or you can use : 

    Measure 9 =
    CALCULATE(
        [Revenue],
        dateadd(DATESMTD('Date Table'[Date]),-1,YEAR)
    )

     

     

     

    let me know if it works for you .

     

     

     

     

    If my response has successfully addressed your issue kindly consider marking it as an accepted solution! This will help others find it quickly. Dont forget to hit thumbs up buttton 👍

     

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        Chanleakna123 

        if you want the same day last year, 

        then modify the measures to this : 

        Measure 9 =
        CALCULATE(
            [Revenue],
            dateadd('Date Table'[Date],-1,Month)
        )

         

        or you can use : 

        Measure 9 =
        CALCULATE(
            [Revenue],
            dateadd('Date Table'[Date],-1,YEAR)
        )

         

         

        let me know if this works for you . 

         

        if it still doesnt work,  plesae share a sample of your pbix file to take a closer look . 

         

        best regards,

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Chanleakna123 

    Follow the same pattern as per this formula:

    MoM = 

VAR __CM = [Sales Amount]
VAR __Period = DATEADD( DATESBETWEEN( 'Date'[Date] , MIN( 'Date'[Date] ) , MAX( 'Sales'[Order Date] ) ) , -1 , YEAR )
VAR __LYSM = CALCULATE( [Sales Amount] ,__Period )
RETURN
  __CM -  __LYSM



     

    • Chanleakna123's avatar
      Chanleakna123
      Post Prodigy
      2 years ago

      Not reflect to what i need , 

      when i do this calculate , it show my MTD is in Negative . 
      i may need MTD this year vs. MTD last year , with the same period day 

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Chanleakna123 

        This is what exactly the formula that I shared does, 
        Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.