cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MontsePuig Frequent Visitor
Frequent Visitor

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. 

ytd-issue2.png

Thank you very much. Smiley Happy

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: YTD calculation

@MontsePuig,

Please review this modified PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
dilumd Established Member
Established Member

Re: YTD calculation

mikeborg82 Regular Visitor
Regular Visitor

Re: YTD calculation

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

 

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

Moderator v-yuezhe-msft
Moderator

Re: YTD calculation

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV
Super User IV

Re: YTD calculation

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MontsePuig Frequent Visitor
Frequent Visitor

Re: YTD calculation

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

 

Moderator v-yuezhe-msft
Moderator

Re: YTD calculation

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MontsePuig Frequent Visitor
Frequent Visitor

Re: YTD calculation

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.

 

wrong.png

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

 

ok.png

 

Thank you very much, Smiley Happy

Moderator v-yuezhe-msft
Moderator

Re: YTD calculation

@MontsePuig,

Please review this modified PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

MontsePuig Frequent Visitor
Frequent Visitor

Re: YTD calculation

Hi Lydia,

 

Now everything is correct. I'm very happy Smiley Happy

Thank you very much! 

 

Montse

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors