Forum Discussion

toxicshev's avatar
toxicshev
Frequent Visitor
3 years ago
Solved

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....
Need Help
  • smpa01's avatar
    smpa01
    3 years ago

    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's avatar
smpa01
Community Champion
3 years ago

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's avatar
    toxicshev
    Frequent Visitor
    3 years ago

    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!