Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have two tabels.
One with historic values of an asset and one with asset purchases.
I want to get the historic values of the asset purchases based on a what if parameter preferably using a measure.
For example if the what if parameter is 5 i would like to get the historic value 5 days earlier. Sometimes there won't be a historic value that specific date and then i would like to get the value of the first date before that date (for example 6 days earlier).
Below are the structure of the tables.
Historic values table:
Asset ID
Date
Price
Asset purchases table:
Asset ID
Transaction date
Price
Solved! Go to Solution.
Try
Historic price =
VAR WhatIf = [What if value]
RETURN
SUMX (
'Asset purchases',
SELECTCOLUMNS (
TOPN (
1,
FILTER (
'Historic values',
'Historic values'[Asset ID] = 'Asset purchases'[Asset ID]
&& 'Historic values'[Date] <= 'Asset purchases'[Date] - WhatIf
),
'Historic values'[Date]
),
"@value", 'Historic values'[Price]
)
)
Thanks 🙂
Try
Historic price =
VAR WhatIf = [What if value]
RETURN
SUMX (
'Asset purchases',
SELECTCOLUMNS (
TOPN (
1,
FILTER (
'Historic values',
'Historic values'[Asset ID] = 'Asset purchases'[Asset ID]
&& 'Historic values'[Date] <= 'Asset purchases'[Date] - WhatIf
),
'Historic values'[Date]
),
"@value", 'Historic values'[Price]
)
)
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |