Need help with Vlookup value in the same datatable with multiple data extration date

[toxicshev]

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!

[smpa01 · Accepted solution]

@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] )

[toxicshev]

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!

[amitchandak]

@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/b...

[toxicshev]

Thanks, but still cant figure it out. Do you mind to write a dax? 

[toxicshev]

Can someone help please? Much appreciated!