Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Maartenrcw
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:

Screenshot 2024-02-03 104434.png

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:
Screenshot 2024-02-03 104656.png
Many thanks for pointing me in the right direction!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_1-1706956935378.png

 

 

Jihwan_Kim_0-1706956918461.png

 

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.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_1-1706956935378.png

 

 

Jihwan_Kim_0-1706956918461.png

 

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.


Go to My LinkedIn Page


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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.