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
viviank
Resolver I
Resolver I

Need to get the most recent value up to the date in the filter context

I have data that is date-based, but we don't have data collected for every date.

 

If the date isn't there for the date, then I want to assume that what we have most recently is good.

 

The data we have looks like this:

 

DateCustomerBalance
7/1/2021Bob100.00
7/2/2021Bob100.00
7/5/2021Bob120.00
7/7/2021Bob90.00
7/1/2021Sally80.00
7/3/2021Sally80.00
7/4/2021Sally80.00

 

When I put this in a matrix visual in Power BI, it looks like this:

 

 7/17/27/37/47/57/67/7
Bob100.00100.00  120.00 90.00
Sally80.00 80.0080.00   

 

There are blanks where we don't have data. What I want instead, is to fill in the data so it looks like this in a matrix visual - with the blanks filled with the last known data (i.e. if we don't have data for Bob for 7/4, we use the data for Bob for 7/2 which is the last info we have for Bob before 7/4).

 

 7/17/27/37/47/57/67/7
Bob100.00100.00100.00100.00120.00120.0090.00
Sally80.0080.0080.0080.0080.0080.0080.00

 

I'm open to doing this via DAX or Power Query. If we could put the values into the table in Power Query, that would work, I'm just not sure how to do that. A DAX formula could work too, not sure how to do that either.

 

Any ideas? Thank you!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@viviank create a calendar dimension in your model and set a relationship with your transaction table, you can follow my blog post to add one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

 

Add the following measure and then in matrix visual, use date from calendar table and on value use the measure:

 

Min = 
CALCULATE ( 
    LASTNONBLANKVALUE ( 'Calendar'[Date], MIN ( 'Table'[Score] ) ), 
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) 
) 

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@viviank you got it, we need some sort of aggregation to use it and I used MIN. Glad you already figured it out. Cheers!!

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@viviank create a calendar dimension in your model and set a relationship with your transaction table, you can follow my blog post to add one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

 

Add the following measure and then in matrix visual, use date from calendar table and on value use the measure:

 

Min = 
CALCULATE ( 
    LASTNONBLANKVALUE ( 'Calendar'[Date], MIN ( 'Table'[Score] ) ), 
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) 
) 

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Brilliant!

Working great!

Do you care to explain why it works? I understand the FILTER - why we need to change the filter context. What I'm not sure about is why you are using MIN ('Table'[Score]) - what does that accomplish in the DAX logic?

 

Thank you!

I think I get it now - we just need to pick one value for the date - I substituted MIN for MAX and both of these work fine.

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