Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I've two rows that I want to compare:
Scenario 1:
In this scenario I want:
- According to the two DateTime most recent, If row 1 from Code = row 2 from Code, I get "False", but, if row 1 from code is different from row 2 from code, and QtdDateInv is null I get "True".
Scenario 1 - I want to get "False":
Customer DateTime Code QtdDateInv C00001 04-02-2017 5245 C00001 02-01-2017 5245 15-01-2017 C00001 25-11-2016 5240 30-11-2016
C00001 12-05-2016 987798 15-05-2016
Scenario 2 - I want to get "True":
Customer DateTime Code QtdDateInv C00031 04-02-2017 6200 C00031 02-01-2017 5245 15-01-2017 C00031 25-11-2016 5240 30-11-2016
C00031 26-01-2016 45455240 31-01-2016
I think that I need to get a mease/calculated column to get the last two Datetime and compare the code. I accept suggestions.
Ty.
Solved! Go to Solution.
Hi @Anonymous,
You can try to use below measure if it suitable for your requirement.
Check = var currDate=MAX([DateTime]) var currCustomer=LASTNONBLANK(Test1[Customer],[Customer]) var currCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],currDate,Test1[Customer],currCustomer) var currQtd=LOOKUPVALUE(Test1[QtdDateInv],Test1[DateTime],currDate,Test1[Customer],currCustomer) var prevCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],MAXX(FILTER(ALL(Test1),[DateTime]<currDate),[DateTime]),Test1[Customer],currCustomer) return if(currCode<>prevCode&&currQtd=BLANK(),TRUE(),FALSE())
Logic : use date and customer to find out the specific records, then compare with them.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can try to use below measure if it suitable for your requirement.
Check = var currDate=MAX([DateTime]) var currCustomer=LASTNONBLANK(Test1[Customer],[Customer]) var currCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],currDate,Test1[Customer],currCustomer) var currQtd=LOOKUPVALUE(Test1[QtdDateInv],Test1[DateTime],currDate,Test1[Customer],currCustomer) var prevCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],MAXX(FILTER(ALL(Test1),[DateTime]<currDate),[DateTime]),Test1[Customer],currCustomer) return if(currCode<>prevCode&&currQtd=BLANK(),TRUE(),FALSE())
Logic : use date and customer to find out the specific records, then compare with them.
Regards,
Xiaoxin Sheng
Hi @Anonymous
The concept of 'previous row' is not so easy to manage in Powerpivot. You need some tricks with Filter, earlier, etc.
I guess the best approach here is to use Power Query with indexes and self-merge.
You can apply the technique @MattAllington shows in this article.
@Datatouille, I think the best approch is comparing the two most recent "DateTime" according to the "Customer" column. But I'm stucked getting the correct result.
Searching in power bi community I found this from @Vvelarde
I adapted to my tables and used "Earlier", but don't work properly. Can anyone help me?
TableWorkSumm = SUMMARIZE ( G_HistRecolha; G_HistRecolha[DataRecolha]; G_HistRecolha[Cliente]; G_HistRecolha[Leitura]; "Resultado"; IF ( CALCULATE( SUM(G_HistRecolha[Leitura] )) - CALCULATE( SUM(G_HistRecolha[Leitura] ); 'G_HistRecolha'[DataRecolha] > EARLIER ( 'G_HistRecolha'[DataRecolha] )) = CALCULATE( SUM( G_HistRecolha[Leitura] )); BLANK(); CALCULATE( SUM(G_HistRecolha[Leitura] )) - CALCULATE( SUM(G_HistRecolha[Leitura] ) ; 'G_HistRecolha'[DataRecolha] > EARLIER ( 'G_HistRecolha'[DataRecolha] )) ) )
and the result expected from the bottom is:
3
2
2
1
1
"BLANK"
1
(...)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |