I would like to do a calculation for:
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...
Solved! Go to Solution.
Could you take a small table and show the expected result on that sample table.
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.
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.
I'm not sure where I can attach spreadsheet so I'm sending you ling to Google spreadsheet.
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...
I receive an access denied message when i click on that link.
You can create measures as shown below:
Sales_YTD = TOTALYTD(SUM(Sales[Amount]),Date[Date])
last year-to-date sales
Sales_LYYTD = TOTALYTD(SUM(Sales[Amount]),SAMEPERIODLASTYEAR(Date[Date]))
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.
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 😞