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

v-yingjl , in terms of performance (As I will have a couple of hundred of thousands of rows in my data set) how will this impact the query itself? Is there no way of building a DAX measure to solve this problem instead?

 

v-yingjl's avatar
v-yingjl
Community Support
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.