cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vilenm
Regular Visitor

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]"

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

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






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

7 REPLIES 7
parry2k
Super User III
Super User III

@vilenm good to hear.






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 III
Super User III

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






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.





vilenm
Regular Visitor

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

parry2k
Super User III
Super User III

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






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

vilenm
Regular Visitor

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
Super User III
Super User III

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






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.





vilenm
Regular Visitor

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.