Forum Discussion

hanyfouda2023's avatar
hanyfouda2023
Icon for Helper II rankHelper II
2 years ago

YTD & MTD report

Hi Helpers,

I attached sample report  because I need to make it from my source data in PBI 

I need to know how to calculate YTD , MTD & Sales performance 

Need Help

9 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    hello hanyfouda2023 

     

    you can use this pattern : 

    MTD : 

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

    YTD : 

    Measure YTD =
    CALCULATE(
        [Revenue],
        DATESYTD('Date Table'[Date]))
    )
     


    these measure will work assumming you have a the appropriate dimdate table in your model .
     
     
     
     
    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit thumbs up button 👍
    • hanyfouda2023's avatar
      hanyfouda2023
      Icon for Helper II rankHelper II
      2 years ago

      Hi Daniel29195,

      It works fine but if I need to make it for year 2023 in the same visual to compare it with 2024 how can i make it because when I select 2023 to 2024 MTD became not accurte

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi hanyfouda2023 ,

    You can use the calculation group to put in the indicators for 2023 and 2024, and just calculate the MTD and YTD to know all the indicators for these two years, for your easy comparison.

    The calculation group DAX might work for you:

    MTD = CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[Date]))


PY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))


YTD = CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))

    Here are some of my examples that can be just replaced with your data

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • hanyfouda2023's avatar
      hanyfouda2023
      Icon for Helper II rankHelper II
      2 years ago

      Hi v-xiandat,

      Thanks for your replay,

      For MTD it works fine but for PY & YTD not accurate, when I seleced year 2023 & 2024 PY & YTD calaculated 2 periods 

      For example if MTD is 10 for 2024 , 10 for 2023 for the same period last year then PY & YTD became 20 instead of 10 

      here is the measures

      PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Datetable[Date]))
      YTD_11 = CALCULATE([Total Revenue], DATESYTD(Datetable[Date]))
      Thanks
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi hanyfouda2023 ,

        Can you send me your pbix file, I'd like to see where the problem is occurring.

        Best Regards,

        Xianda Tang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.