Forum Discussion

Sefino535's avatar
Sefino535
Frequent Visitor
5 years ago
Solved

Measure for YTD %, YTG %

Hello,

 

I would like to do a calculation for:

  • YTD % (if we are in March sum up Jan, Feb, March and compare with last year Jan, Feb, March and gibe me grow rate in %)
  • YTG % (if we are in March sum up April, May ... Dec and compare with last year April, May ... Dec and gibe me grow rate in %)
  • And also the same calculation for Quaters. 

 

Could you please help me? Without "hard filters" more like a measure so I can have it in one metrix row.

 

Thank you a lot!

 

Sometining like this...

 

 

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You can create measures as shown below:

    year-to-date sales

    Sales_YTD = TOTALYTD(SUM(Sales[Amount]),Date[Date])

    last year-to-date sales

    Sales_LYYTD = TOTALYTD(SUM(Sales[Amount]),SAMEPERIODLASTYEAR(Date[Date]))

    TOTALYTD and SAMEPERIODLASTYEAR are dax functions.

    Using the above measures, you can calculate growth YTD%. For YTG see below:

    Sales_YTG = CALCULATE ( SUM ( 'Table'[Sales] ), DATESBETWEEN ( 'Table'[Date], TODAY(), ENDOFYEAR ( 'Table'[Date].[Date] ) ) )

     

    Similarly, last year year to go can be:

    Sales_LYYTG = CALCULATE ( SUM ( 'Table'[Sales] ), DATESBETWEEN ( 'Table'[Date], SAMEPERIODLASTYEAR(TODAY()), SAMEPERIODLASTYEAR(ENDOFYEAR ( 'Table'[Date].[Date] ))))

    Based on above two measures you can easily create YTG%

     

    There might be shorter way to do this but I am not sure. Let me know if these work for you. Do mark the post as solution and give kudo if I have helped you.

    • Sefino535's avatar
      Sefino535
      Frequent Visitor
      5 years ago

      Hi,

       

      Your calculation for YTD and LY_YTD it give me acumulative totals and if I just want to compare only last 3 montsh of the year vs last Year first 3 months. 

       

      This is wat I got base on your calculations. I would like to select only first 3 months in green. 

       

      Column YTD2 is base on calendar filer like this, but this way I don't know how to retrieve Last Year YTD.

      Any idea how can I do it this way?

       

      For YTG, I forgot to mention I work with monthly calendar and your Today() formula won't work. Any Idea how to get dinamic first day of the month?

       

      Thank you for your time! I've been looking everywhere 😞

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Could you take a small table and show the expected result on that sample table.

    • Sefino535's avatar
      Sefino535
      Frequent Visitor
      5 years ago

      Hi,

      I below is simulation of what I want to achieve.

      The importance in my calculations is to have exact same range of months I want to retrieve and compare from last year. (most of timeinteligens as TOTALYTD does acumulative sum till the end of year, as I just need values till current month for comparison  or next month till the end of year)

       

      YTD and YTG will be dynamic based on current month. It will show how product/category perform untill current month and look at trend for future forecast.

       

      *just a note my calendar is in month bucket

       

      This calculation then I want to apply on YTD%, YTG%, FY % and Qaurters %. 

      I hope it make sense or feel free to ask any more questions. 

       

      Martin

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        How did you calculate the %'s in the % calculation row?  Please share the download link of the Excel file with your formulas in there.

        Also, while i cannot get the exact structure as you want, i can get the information you may want.