Forum Discussion
% Gap Closure
Hello! looking for the best formula/measure to calculate % Gap Closure over a year.
I know it should be something like this (skill rating 2- skill rating 1)/ Goal
for example;
2019 / 2020 / goal % Gap Closure
skill rating A 1 2 3 *(2-1)/3 =* 33%
here is a sample of my excel sheet;
Anonymous , something like this
This Year = CALCULATE(SUM(Table[Rating]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD complete = CALCULATE(SUM(Table[Rating]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Goal = CALCULATE(SUM(Table[Goal])) measure = divide(([This Year] - [Last YTD complete ]),[Goal])
3 Replies
Anonymous
With date calendar and time intelligence, you can have this year and last year data like this
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Anonymous , something like this
This Year = CALCULATE(SUM(Table[Rating]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD complete = CALCULATE(SUM(Table[Rating]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Goal = CALCULATE(SUM(Table[Goal])) measure = divide(([This Year] - [Last YTD complete ]),[Goal])