Skip to main content
cancel
Showing results for 
Search instead 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

Reply
GabinAM
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 -.

Cumulative Sum.PNG

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.
 
Expected Result.PNG

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
Ahmedx
Super User
Super User

Greg_Deckler
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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
GabinAM
Frequent Visitor

Many thanks @Ahmedx and @Greg_Deckler it works! 🎉

Greg_Deckler
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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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

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

and disabled date hierarchies

Ahmedx
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors