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

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...

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Hi,

Download my PBI file from here.  This should get you started.

Untitled.png


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

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

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


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

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.

 

PBI_3.JPG

*just a note my calendar is in month bucket

 

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

PBI_4.JPG

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

 

Martin

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.


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

Hi Ashish,

 

I'm not sure where I can attach spreadsheet so I'm sending you ling to Google spreadsheet.

Calculate YTD_YTG 

 

But the calculation is easy:

1. There are sums of YTD = First month of year till current, same for last year perod

2. Formula % calculation: SUM(YTD) / SUM(LY_YTD) - 1

3. Same apply for YTG logic.

 

I hope is clear now. 

It would be great if the formula will get YTD and YTG from all the years not just 2020. For example if I select last year 2020, I will get comparison from 2019 and so on... 

 

Thank you,

Martin

Hi,

I receive an access denied message when i click on that link.


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

Hi Ashish,

 

Please try this link... 

Excel link YTD_YTG 

Hi,

Download my PBI file from here.  This should get you started.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
gauravtanwar
Resolver II
Resolver II

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.

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. 

PBI1.jpg

 

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?

PBI2.jpg

 

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 😞

 

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors