YTD calculation

[MontsePuig]

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. 

[Ashish_Mathur]

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.

[Anonymous]

@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]

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]

@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]

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, 

[Anonymous · Accepted solution]

@MontsePuig,

Please review this modified PBIX file.

Regards,
Lydia

[MontsePuig]

Hi Lydia,

Now everything is correct. I'm very happy 

Thank you very much! 

Montse

[mikeborg82]

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

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

[dilumd]

Hi,

use DATESYTD function.

https://msdn.microsoft.com/en-us/library/ee634221.aspx