Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
toxicshev
Frequent Visitor

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 IDMembership End dateData extraction date
1Oct/31/2022Oct/31/2022
2Oct/31/2022Oct/31/2022
3Oct/31/2022Oct/31/2022
4Oct/31/2022Oct/31/2022
5Oct/31/2022Oct/31/2022
6Oct/31/2022Oct/31/2022
7Dec/31/2022Oct/31/2022
8Jan/31/2023Oct/31/2022
9Mar/31/2023Oct/31/2022
10Feb/28/2023Oct/31/2022
11Apr/30/2023Oct/31/2022
5Nov/30/2023Nov/30/2022
6Nov/30/2023Nov/30/2022
7Dec/31/2022Nov/30/2022
8Jan/31/2023Nov/30/2022
9Mar/31/2023Nov/30/2022
10Feb/28/2023Nov/30/2022
11Apr/30/2023Nov/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!
2023-02-23_10-43-59.png

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

 

smpa01_0-1677777533042.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

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

 

smpa01_0-1677777533042.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Super User
Super User

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Can someone help please? Much appreciated! 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.