Get sum of previous date when there are gaps in date column (Measure)

[Anonymous]

So I've been stuggling with this, seems like quite a simple concept but that does seem to happen a lot with PBI

Here's some example data to paint a picture:

Client	Date	Sale
A	11/09/2021	100
B	11/09/2021	250
A	13/09/2021	300
B	14/09/2021	250

There's a date filter on the page I'm trying to get this measure working on filtered to 13/09/2021. I'm trying to create a card visualisation displaying a sum of the sales on the previous date in the table based on this filter (so sum should be 350).

PREVIOUSDAY doesn't work here, it only works when I set the filter to 14/09/2021. So I need a more dynamic solution for days when there are no sales in the database.

Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

Hi @Anonymous 

Get the previous date first

preDaySale = 
VAR CurDay = SELECTEDVALUE(yourTable[Date])
VAR PreDay = MAXX(FILTER(ALL(yourTable),yourTable[Date]<CurDay),yourTable[Date])
RETURN
SUMX(FILTER(ALL(yourTable),yourTable[Date]=PreDay),yourTable[Sale])

[Anonymous]

This worked, thank you very much! I hope others find this solution too as I spent a long time searching the forum to no avail.