Forum Discussion

MontsePuig's avatar
MontsePuig
Frequent Visitor
8 years ago
Solved

YTD calculation

Hi everyone,

 

I need to calculate:

1.-  YTD both columns 2016 and 2017

2.-  % variation 2017 vs 2016  should be -17% 2016=209.300, but it's wrong.

 

This is the actual formula, the 2017 is correct data but 2016 should be YTD 5/11/2017 too.

2016=CALCULATE(SUM('table'[transactions];'table'[year] IN { 2016})

2017=CALCULATE(SUM('table'[transactions];'table'[year] IN { 2017})

 

¿What is the correct calculation YTD?

 

This is a date table example. 

Thank you very much. :smileyhappy:

 

 

Need Help

9 Replies

  • mikeborg82's avatar
    mikeborg82
    Advocate II
    8 years ago

    Measure = CALCULATE ( Value , DATESYTD ( 'Date' [Date]))

     

    Assume you have a date dimension table named "Date".  This should work for you...

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    MontsePuig,

    Could you please share the raw sample data of your table? We need to verify if the Month field comes from your fact table or date table.

    Besides, I am not very clear about the logic you use to calculate  % variation 2017 vs 2016 , how do you get -17% and 209300 based on the data in the above Matrix visual?

    Regards,
    Lydia

    • MontsePuig's avatar
      MontsePuig
      Frequent Visitor
      8 years ago

      Hi Lydia, Thank you very much for your help.

       

      Here you are the raw data link, I have used Matrix Visualization.

      % variation 2017 vs 2016 MTD should be -17% , calculation (174.350 /209.300)-1

      % variation 2017 vs 2016 YTD should be 28%, calculation (16.556320/12.929.820)-1

       

      Montse

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        MontsePuig,

        Create the following columns in your table.

        max = MAX(Hoja1[Date])

        Column = DATE(YEAR(Hoja1[max])-1,MONTH(Hoja1[max]),DAY(Hoja1[max]))

        Then create the following measures in your table.

        YTD For 2016 = CALCULATE(SUM(Hoja1[Transactions]),FILTER(Hoja1,Hoja1[Date]<=MAX(Hoja1[Column])))

        YTD For 2017 = CALCULATE(SUM(Hoja1[Transactions]),FILTER(Hoja1,YEAR(Hoja1[Date])= YEAR(TODAY())))

        % Variation 2017 vs 2016 YTD = ([YTD For 2017]/[YTD For 2016])-1


        2016Nov = CALCULATE(SUM(Hoja1[Transactions]),FILTER(Hoja1,Hoja1[Date]<=MAX(Hoja1[Column]) && MONTH(Hoja1[Date])=MONTH(TODAY())))

        2017Nov = CALCULATE(SUM(Hoja1[Transactions]),FILTER(Hoja1,YEAR(Hoja1[Date])= YEAR(TODAY())&&MONTH(Hoja1[Date])=MONTH(TODAY())))

        % Variation 2017 vs 2016 for Nov = ([2017Nov]/[2016Nov])-1



        Regards,
        Lydia

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    In your image, the YTD transactions have declined in March 2016 and April 2017.  How is that possible?  Share your sample dataset and also show the expected result.