Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi - I have been stuck on this seemingly simple problem for the last couple of days...
I have a table that stores changes in attributes of objects (Deal ID) that looks like this:
I have a separate DateDim table and I now need a measure to perform a calculation on the most recent historical state based on a Date slicer.
So for example on 31/12/2023 Deal ID 25 had an amount 90000 and stage D, and Deal ID 13 had an amount 30000 and stage C.
The measure should then show this result:
Many thanks for pointing me in the right direction!
Solved! Go to Solution.
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _currentstage =
VALUES ( Stage[Stage] )
VAR _t =
SUMMARIZE (
ALL ( Data ),
'Calendar'[Date],
DealID[DealID],
Stage[Stage],
Data[Amount]
)
VAR _lastnonblankdate =
ADDCOLUMNS (
_t,
"@lastnonblankdate",
MAXX (
FILTER (
_t,
DealID[DealID] = EARLIER ( DealID[DealID] )
&& 'Calendar'[Date] <= _currentdate
&& Data[Amount] <> BLANK ()
),
'Calendar'[Date]
)
)
VAR _filter =
SUMMARIZE ( _lastnonblankdate, DealID[DealID], [@lastnonblankdate] )
RETURN
SUMX (
FILTER (
CALCULATETABLE (
SUMMARIZE ( Data, 'Calendar'[Date], DealID[DealID], Stage[Stage], Data[Amount] ),
TREATAS ( _filter, DealID[DealID], 'Calendar'[Date] )
),
Stage[Stage] IN _currentstage
),
Data[Amount]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _currentstage =
VALUES ( Stage[Stage] )
VAR _t =
SUMMARIZE (
ALL ( Data ),
'Calendar'[Date],
DealID[DealID],
Stage[Stage],
Data[Amount]
)
VAR _lastnonblankdate =
ADDCOLUMNS (
_t,
"@lastnonblankdate",
MAXX (
FILTER (
_t,
DealID[DealID] = EARLIER ( DealID[DealID] )
&& 'Calendar'[Date] <= _currentdate
&& Data[Amount] <> BLANK ()
),
'Calendar'[Date]
)
)
VAR _filter =
SUMMARIZE ( _lastnonblankdate, DealID[DealID], [@lastnonblankdate] )
RETURN
SUMX (
FILTER (
CALCULATETABLE (
SUMMARIZE ( Data, 'Calendar'[Date], DealID[DealID], Stage[Stage], Data[Amount] ),
TREATAS ( _filter, DealID[DealID], 'Calendar'[Date] )
),
Stage[Stage] IN _currentstage
),
Data[Amount]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This is amazing, it is exactly what I was looking for. I will need to study it for a while, because I don't think I would have ever gotten there by myself. Thank you so much Jihwan_Kim!
User | Count |
---|---|
57 | |
48 | |
18 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
40 | |
28 | |
22 |