cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## If blank, then take last non blank value (Matrix/Measure)

Hello all,

I would like to compute the Cumulative Sum of PNL for a set of clients on a Daily Basis (it's a test table).

For now, I succeed to compute the Cumulative Sum per Clients - please find below -.

However, I would like to have the blank value to be filled by the last non blank measure. More precisly :
• If X is blank, then
• X = Last non Blank Measure
(if no last value, then 0, please see column D and E below)
Please find below the expected result with in blue the desired values per cell.

I know that this type of problem has already been the subject of different posts... and I already tried a lot of differents possibilities but unfortunately, no success for now...

Please find the Table and the PowerBI document here : https://www.dropbox.com/t/5VLKClEANs7xkbY8

Many thanks for your help and please let me know if any questions ! 😊

GAM
2 ACCEPTED SOLUTIONS
Super User
Super User

@GabinAM Did the same thing as @Ahmedx apparently. You were running into AutoExist issues and all kinds of other problems. Created a running total measure and after cleaning up the dataset everything is working. PBIX is attached below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Frequent Visitor

Many thanks @Ahmedx and @Greg_Deckler it works! 🎉

Super User

@GabinAM Did the same thing as @Ahmedx apparently. You were running into AutoExist issues and all kinds of other problems. Created a running total measure and after cleaning up the dataset everything is working. PBIX is attached below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

I normalized your model, removes duplicates in power query and my connection became 1 to many

and disabled date hierarchies

Super User