Forum Discussion
Need help with Vlookup value in the same datatable with multiple data extration date
Hi Everyone,
I've been struggling with this for awhile, and I cant seem to figure out how to solve this in DAX. I need help!
A table below is my dataset that contains data from 2 extration date.
What I'm trying to achieve is below:
As of Oct/31/2022(data extration date), I have a total of 11 memebership ID, 2 of them(ID: 5 and 6) that their membership exprires on Oct/31/2022,
When I pulled the data a month later on Nov/30/2022 (data extration date) and append with the date from prevous extration date, since ID 1, 2, 3, 4 did renew their membership, only ID 5, and 6 have been renewed their membership with a new "membership end date" as Nov/30/2023
| Membership ID | Membership End date | Data extraction date |
| 1 | Oct/31/2022 | Oct/31/2022 |
| 2 | Oct/31/2022 | Oct/31/2022 |
| 3 | Oct/31/2022 | Oct/31/2022 |
| 4 | Oct/31/2022 | Oct/31/2022 |
| 5 | Oct/31/2022 | Oct/31/2022 |
| 6 | Oct/31/2022 | Oct/31/2022 |
| 7 | Dec/31/2022 | Oct/31/2022 |
| 8 | Jan/31/2023 | Oct/31/2022 |
| 9 | Mar/31/2023 | Oct/31/2022 |
| 10 | Feb/28/2023 | Oct/31/2022 |
| 11 | Apr/30/2023 | Oct/31/2022 |
| 5 | Nov/30/2023 | Nov/30/2022 |
| 6 | Nov/30/2023 | Nov/30/2022 |
| 7 | Dec/31/2022 | Nov/30/2022 |
| 8 | Jan/31/2023 | Nov/30/2022 |
| 9 | Mar/31/2023 | Nov/30/2022 |
| 10 | Feb/28/2023 | Nov/30/2022 |
| 11 | Apr/30/2023 | Nov/30/2022 |
is there a way (measure or other ways) to achieve:
calulating the number of ID that did not renew in the latest data extration date?
in the example above, it would be something like: 4(ID 1,2,3,4) out 6(ID: 1,2,3,4,5,6) that did not renew,
when the membership end date= data extration date.
In other words, I have to write some dax to lookup that ID 1, 2, 3, 4,5,6 from the previous data extration date(oct/31/2022) to see if they show up in the new date extration date (nov/30/2022), only ID 5 and 6 appears in the new data extration (nov/30/2022) because their membership has renewed(now with new membership end date as nov/30/2023 comparing to oct/31/2022 in the previous data extration date)
see below screenshot for done in excel manually with vlookup.
Thanks in advance for your help!
toxicshev you can use something like this
Measure:= VAR immediatelyPrecedingExtractionDate = CALCULATE ( [extractionDate], ALL ( 'fact' ), OFFSET ( -1, ALL ( 'fact'[Data extraction date] ), ORDERBY ( 'fact'[Data extraction date], ASC ) ) ) VAR _left = SUMMARIZE ( FILTER ( ALL ( 'fact' ), 'fact'[Data extraction date] = immediatelyPrecedingExtractionDate ), 'fact'[Membership ID] ) VAR __right = VALUES ( 'fact'[Membership ID] ) VAR _anti = EXCEPT ( _left, __right ) RETURN COUNTX ( _anti, [Membership ID] )
5 Replies
toxicshev you can use something like this
Measure:= VAR immediatelyPrecedingExtractionDate = CALCULATE ( [extractionDate], ALL ( 'fact' ), OFFSET ( -1, ALL ( 'fact'[Data extraction date] ), ORDERBY ( 'fact'[Data extraction date], ASC ) ) ) VAR _left = SUMMARIZE ( FILTER ( ALL ( 'fact' ), 'fact'[Data extraction date] = immediatelyPrecedingExtractionDate ), 'fact'[Membership ID] ) VAR __right = VALUES ( 'fact'[Membership ID] ) VAR _anti = EXCEPT ( _left, __right ) RETURN COUNTX ( _anti, [Membership ID] )Thanks a lot, much appreciated your help!
Tested in Power BI, it worked perfectly.
Any chance you know if I can replace OFFSET with an alternative in Power Pivot?Thanks again for your help!
toxicshev , You should create measures based on Data extraction date having Membership End date>= Data extraction date
then we can create a current/prior measures (monthly( and check retained
example
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
