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

[viviank]

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:

Date	Customer	Balance
7/1/2021	Bob	100.00
7/2/2021	Bob	100.00
7/5/2021	Bob	120.00
7/7/2021	Bob	90.00
7/1/2021	Sally	80.00
7/3/2021	Sally	80.00
7/4/2021	Sally	80.00

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

	7/1	7/2	7/3	7/4	7/5	7/6	7/7
Bob	100.00	100.00			120.00		90.00
Sally	80.00		80.00	80.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/1	7/2	7/3	7/4	7/5	7/6	7/7
Bob	100.00	100.00	100.00	100.00	120.00	120.00	90.00
Sally	80.00	80.00	80.00	80.00	80.00	80.00	80.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!

[parry2k]

@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.⚡

[parry2k · Accepted solution]

@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.⚡

[viviank]

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!

[viviank]

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.