Forum Discussion
Advocate IComparison vs last year on same perimeter
Eureka , Try a measure like
Diff =
Var _ytd = [ytd]
car _lytd = [LYTD]
return
sumX(Values(Table[Customer]), if(not(isblank(_ytd) ) && not(isblank(_lytd) ) , _ytd-_lyd, blank()) )
Super UserEureka , You can create trailing measure
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
you can try measures like
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
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 :radacad sqlbi My Video Series Appreciate your Kudos.
EurekaHello amitchandak ,
I have already tried those measures but it is not removing the quantity of the customer B in the volume. I want to remove customer B because this customer have no data for January 2022 and I dont want to take its volume to compare with January 2021.
I hope I express myself clearly,
Thank you
- amitchandak
Eureka , Try a measure like
Diff =
Var _ytd = [ytd]
car _lytd = [LYTD]
return
sumX(Values(Table[Customer]), if(not(isblank(_ytd) ) && not(isblank(_lytd) ) , _ytd-_lyd, blank()) )