cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viviank
Helper I
Helper 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.






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.






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.






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

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!