Filldown measure for each Id and last status is open

[ruanolin]

I have the “changes” table that stores Estimated and Real hour changes for each ID from when the project opens until it closes. I’m trying to create a measure that gives me the last value of that column for all days.

The following measure provides the expected result, except for days when an ID has no entries but another ID does (for example, on 2024-02-10😞

Real_Filldown = 
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( changes[Date] ),
        FILTER (
             ALLEXCEPT( changes , changes[Id]),
            changes[Date] <= MAX ( changes[Date] )
                && changes[Real] <> 0
                
        )
    )
RETURN
    CALCULATE (
        SUM ( changes[Real] ),
        FILTER ( ALLEXCEPT( changes , changes[Id] ), changes[Date] = LastNonBlankDate )
    )

How can I modify this measure to get the last value of “Real” for days when that date doesn’t exist for a specific ID but does for another (for example, 2024-02-10)?

Additionally, I’d like the “Real_Filldown” measure to only fill in data for days when the project is from “Open” to “Closed.”

I attach my example files: https://easyupload.io/y2vf3y

[Anonymous · Accepted solution]

Hi @ruanolin ,

Depending on the information you have provided, you can follow these steps below:

1.Add new table with not relationship.

Table = DISTINCT('changes'[Date])

2.Add new measures.

Real_Filldown 2 = 
VAR __LastNonBlankDate =
    CALCULATE (
        MAX ( changes[Date] ),
        'changes'[Date] <= MAX ( 'Table'[Date] ),
        changes[Real] <> 0,
        ALLEXCEPT ( changes, changes[Id] )
    )
VAR __result =
    CALCULATE (
        SUM ( changes[Real] ),
        FILTER ( ALLEXCEPT ( changes, changes[Id] ), changes[Date] = __LastNonBlankDate )
    )
RETURN
    __result

Real_LastNonBlankDate 2 = 
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( changes[Date] ),
        FILTER (
             ALLEXCEPT( changes , changes[Id]),
            changes[Date] <= MAX ( 'Table'[Date] )
                && changes[Real] <> 0
        )
    )
RETURN 
LastNonBlankDate

Total Real = 
VAR __cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR __result =
    CALCULATE ( SUM ( 'changes'[Real] ), 'changes'[Date] = __cur_date )
RETURN
    __result

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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