Forum Discussion

SEMattis's avatar
SEMattis
Advocate III
5 years ago
Solved

Lookup historical value based on nearest snapshot date compared to end date

Hi,   I'm working on developing a Power BI solution for co-worker turnover which may seem simple at first. However, since I'm working in an organisation that keeps on changing labels that I need fo...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi SEMattis ,

    If you want to create a DAX measure, you need to create a relationship between these tables based on the cost center field.

    Create a date measure like this:

    _Date =
MIN ( 'Leaver Data-DAX'[End Date] )
    + DATEDIFF (
        SELECTEDVALUE ( 'Leaver Data-DAX'[End Date] ),
        CALCULATE (
            MIN ( 'Org Data-DAX'[Snapshot Date] ),
            ALLEXCEPT ( 'Org Data-DAX', 'Org Data-DAX'[Cost Center] )
        ),
        DAY
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SEMattis's avatar
SEMattis
Advocate III
5 years ago

Wow, this certainly looks complex for such a small case. Is there no way of utilizing DAX instead of PQ to solve the problem? 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

No idea why you'd prefer DAX to PQ since in PQ it's easier (contrary to what you think) and the table gets optimized (it gets the best compression, thus DAX is faster). Calculated columns in DAX should always be the very last resort. You don't have this need here. Just copy my code and paste it into PQ. Just like that. Paste it into the left-hand side (right-click) where you see all the tables' names, function names and parameter names.