Forum Discussion
DAX evaluate previous date based on unsequential random dates
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]"
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.⚡
7 Replies
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.⚡
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?
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.⚡
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?
My apologies, I got it to work. Thank you for your brilliance
