DAX evaluate previous date based on unsequential random dates

[vilenm]

Hi

I have a [Load Date] Column with 3 dates 2020/08/1, 2020/10/22,2021/01/13. What would be the dax I would use to determine the previous date from the latest date. I attempted Previous Date = CALCULATE(SUM(Sales),FILTER(Table1,Table1[Load Date]<EARLIER(Table1[Load Date])-1))).

But EARLIER doesnt pick up the "Table1[Load Date]"

[parry2k]

@vilenm good to hear.

[parry2k]

@vilenm as I said share sample data and expected out rather than getting lost in this communication. 😀

[vilenm]

My apologies, I got it to work. Thank you for your brilliance

[parry2k · Accepted solution]

@vilenm yes it should, if not then share sample data to look into it further. Test it out first and let's go from there.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

[vilenm]

Hi . The issue I have is that it seems to aggregate all sales less than the max date where I only need it for 2020/10/22, seems to include 2020/08/31 as well.  It should evaluate the column and determine from the array of dates presented what would be considered the previous date from the latest date so not sure if  "<" would work?

[parry2k]

@vilenm so you want sales for the previous date, correct?

Prev Load Date Sales = 
VAR __currentDate = MAX ( Table[Load Date] )
VAR __prevDate = CALCULATE ( MAX ( Table[Load Date] ), Table[Load Date] <= __currentDate )
RETURN
CALCULATE ( SUM ( Table[Sales] ), ALL ( Table ), Table[Load Date] = __prevDate )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

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

[vilenm]

Thank you for your impressive response!!! Just to confirm..

So the previous date in the example I gave would be then all sales for 2020/10/22 because max date i have is 2021/01/13?