Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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/
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.