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 cor...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    MontsePuig,

    Please review this modified PBIX file.

    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

  • MontsePuig's avatar
    MontsePuig
    Frequent Visitor
    8 years ago

    Hi Lydia, thank you very much for your help.

     

    I understand your idea, I have reproduced your instructions but I think something is wrong. The result of YTD For 2016 and YTD For 2017 are very high numbers.

     

    In my example, the numbers are correct but I don't know how to calculate variation % YTD in a current Month (November)

    Here you are a .pbix file

     

     

    Thank you very much, :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      MontsePuig,

      Please review this modified PBIX file.

      Regards,
      Lydia

      • MontsePuig's avatar
        MontsePuig
        Frequent Visitor
        8 years ago

        Hi Lydia,

         

        Now everything is correct. I'm very happy :smileyhappy:

        Thank you very much! 

         

        Montse