Forum Discussion
Advocate IComparison vs last year on same perimeter
Hello,
I want to create a metric to compare Sales Qty with last year but I want to remove the customers for which I have data for last year and not for this year.
here is my table:
| Customer Id | Date | Qty |
| A | 1/01/22 | 10 |
| A | 1/01/21 | 50 |
| B | 1/01/21 | 20 |
In my example If I compare 2022 with 2021 I will have 10 vs 70, but I want to exclude the customer B so I can have 10 vs 50 .
Do you have an idea about how we can do that in Power BI?
Thank you in advance,
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()) )
4 Replies
- amitchandak
Super User
Eureka , 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()) )
- Eureka
Thank you amitchandak
I am using the sumx you propose and it's working fine for one month but it doen't solve my problem:
If I have several month in my table:
Customer Id Date Qty A 1/01/22 10 A 1/01/21 50 A 1/02/21 25 B 1/01/21 20 B 1/02/21 15 B 1/02/22 30 If in my report I select the months january 22 and february 22, the formula will include A and B . I have 40 vs 110 instead of 40 vs 75.
In this case I want to remove B for january and remove A for february .
Do you have an idea how I can do it ?