cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Measure on historical states

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!

1 ACCEPTED SOLUTION
Super User

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 =
_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.

2 REPLIES 2
Super User

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 =
_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.

Frequent Visitor

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors