Filldown measure for each Id and last status is open

ruanolin — Mon, 15 Apr 2024 14:15:26 GMT

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

Re: Filldown measure for each Id and last status is open

Anonymous — Tue, 16 Apr 2024 08:41:06 GMT

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.

topic Re: Filldown measure for each Id and last status is open in DAX Commands and Tips

Filldown measure for each Id and last status is open

Re: Filldown measure for each Id and last status is open