Measure for YTD %, YTG %

[Sefino535]

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

[Ashish_Mathur]

Hi,

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

[Sefino535]

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.

*just a note my calendar is in month bucket

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

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

Martin

[Ashish_Mathur]

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.

[Sefino535]

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

[Ashish_Mathur]

Hi,

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

[Sefino535]

Hi Ashish,

Please try this link... 

Excel link YTD_YTG 

[Ashish_Mathur · Accepted solution]

Hi,

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

[gauravtanwar]

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.

[Sefino535]

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. 

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?

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 😞