DAX: Filter Measures by a Selected Date, that Lookup the corresponding value Between Dates

[Titatovenaar]

Hi all!

I have a Date filter on my screen on which I can select a certain reference date, and based on what date I select I want my measure 'ValuationA' and 'ValuationB' to return a certain value that was Valid during that selected date.

Table1 'ObjectValidity'

ObjectID	Type	ValidFrom	ValidTo	Valuation
1	A	1-1-2015	31-12-2016	5000
1	A	1-1-2018	31-12-2018	7500
1	B	1-1-2015	31-12-2019	4000

Table2 'Calendar_NoRelation' (No relationship set to the other table. Hopefully we can work around this!).

Measure 'ValuationA'

ValuationA = 
    CALCULATE (
        MAX ( ObjectValidity[VALUATION] );
        FILTER (
            ( ObjectValidity);
            SELECTEDVALUE ( Calendar_NoRelation[Date] ) >= ObjectValidity[VALIDFROM] &&
            SELECTEDVALUE ( Calendar_NoRelation[Date] ) <= ObjectValidity[VALIDTO] &&
            ObjectValidity[Type] = "A"
        )
    )

Measure 'ValuationB'

ValuationB = 
    CALCULATE (
        MAX ( ObjectValidity[VALUATION] );
        FILTER (
            ( ObjectValidity);
            SELECTEDVALUE ( Calendar_NoRelation[Date] ) >= ObjectValidity[VALIDFROM] &&
            SELECTEDVALUE ( Calendar_NoRelation[Date] ) <= ObjectValidity[VALIDTO] &&
            ObjectValidity[Type] = "B"
        )
    )

Expected result:

When I select 1-1-2019 I expect:

ObjectID	ValuationA	ValuationB
1	NULL	4000

When I select 1-1-2016 I expect:

ObjectID	ValuationA	ValuationB
1	5000	4000

Here the columns are the following:

Column 1 ObjectID (column from Table1 'ObjectValidity')

Column 2 ValuationA (measure)

Column 3 ValuationB (measure)

Currently it does not show any value, anyone knows why that is and what you should change to make it work?

Kind regards,

Igor 

[v-yingjl]

Hi @Titatovenaar ,

You can modify your measure like this:

ValuationA = 
VAR _date =
    SELECTEDVALUE ( Calendar_NoRelation[Date] )
VAR _vA =
    CALCULATE (
        MAX ( 'ObjectValidty'[Valuation] ),
        FILTER (
            ALL ( 'ObjectValidty' ),
            _date >= 'ObjectValidty'[Valid From]
                && _date <= 'ObjectValidty'[Valid To]
                && 'ObjectValidty'[Type] = "A"
        )
    )
RETURN
    IF ( _vA <> BLANK (), _vA, "NULL" )

ValuationB = 
VAR _date =
    SELECTEDVALUE ( Calendar_NoRelation[Date] )
VAR _vB =
    CALCULATE (
        MAX ( 'ObjectValidty'[Valuation] ),
        FILTER (
            ALL ( 'ObjectValidty' ),
            _date >= 'ObjectValidty'[Valid From]
                && _date <= 'ObjectValidty'[Valid To]
                && 'ObjectValidty'[Type] = "B"
        )
    )
RETURN
    IF ( _vB <> BLANK (), _vB, "NULL" )

When you use date slicers you will get your expected result:

Attached my sample file that hopes to help you, please check and try it: DAX: Filter Measures by a Selected Date, that Lookup the corresponding value Between Dates.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Titatovenaar]

Thanks for the fast reply, in your .pbix it indeed works!

When I apply it to my own .pbix file it does not work somehow, probably because of a bit more complex relations then the testdate I included I'm afraid.

I threw out most of the actual report to leave a subset of the actual data I really work with, can you maybe check why in this case it does not work? Somehow the current solution in the file for another problem, seems te be causing an issue for your solution to work with it simultaneously.

https://1drv.ms/u/s!AliMmiR5bX0C3QOmsFM0t9knUQt6?e=O4aMCw

Instead of the ObjectValidity, in my example in the testfile, it concerns the 'ObjectValuation' table!

ObjectValidity in the report is used for something, else, that already works fine. (it filters the rows of the table when making a certain selection. It only shows the Objects that are valid in a certain period. Now I want the Valuation to be shown with it).

Kind regards,

Igor

[v-yingjl · Accepted solution]

Hi @Titatovenaar ,

In your sample file, I have found some problems in your data table:

When the [VALCODE_TYPEID] = 'MARKTWRD', the date in [VALIDTO] column is eariler than [VALIDFROM] column so the measure ValuationA will always show null under the previous logic.

Just modify the comparision logic of ValuationA:

ValuationA = 
VAR _date =
    SELECTEDVALUE ( Calendar_NoRelation[Date] )
VAR _vA =
    CALCULATE (
        MAX ( 'ObjectValuation'[Valuation] ),
        FILTER (
            ALL ( 'ObjectValuation' ),
            _date >= 'ObjectValuation'[VALIDTO]
                && _date <= 'ObjectValuation'[VALIDFROM]
                && 'ObjectValuation'[VALCODE_TYPEID] = "MARKTWRD"
        )
    )
RETURN
    IF ( _vA <> BLANK (), _vA, "NULL" )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Of course! Thanks a lot, this works like a charm now 🙂

[v-yingjl]

Hi @Anonymous ,

That's my pleasure😄. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li