Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
elenton
Helper II
Helper II

Previous Days Orders

Hi
I want to get all orders from the previous day. 

However if i use now() - 1 it pulls the time in which dosent link on my date table.

If i format the date it ends up as a string whcih is incompatable with the date its trying to link with.

How do i go about doing this. Below is one example i was trying to use

_Daily Sales =

var CurrentDay = DATE(YEAR(NOW()), MONTH(NOW()) , DAY(NOW()) -1)


Return

calculate(
count('Sales'[OrderID]),
_Date[Date]=CurrentDay)



1 ACCEPTED SOLUTION

@elenton seems lke you have date dimension in your model, if yes then update measure

 

Prev day sales = 
CALCULATE ( SUM( Table[Sales] ), 
PREVIOUSDAY( Date[Date] ) 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

8 REPLIES 8
parry2k
Super User
Super User

@elenton not sure whst is the question and what is the issue with your approach?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 sorry i missed that bit.

I think the issue may be that the date format i am trying to link on is YYYY-MM-DD which is different to the fomat that the variable is producing.

When i try to format the variable the same it is then converted to a string so data type mismatch. So im not sure how to then convert it back into a date.

 

Or is there something more obvious im missing?

@elenton what exact error your are getting? Date function you used for var should return a date.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 this is the error that comes up

Error Message:
MdxScript(Model) (63, 5) Calculation error in measure '_Alert Measures'[_Daily Calls Total]:
DAX comparison operations do not support comparing values of type Date with values of type Text.
Consider using the VALUE or FORMAT function to convert one of the values.

@elenton seems lke you have date dimension in your model, if yes then update measure

 

Prev day sales = 
CALCULATE ( SUM( Table[Sales] ), 
PREVIOUSDAY( Date[Date] ) 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 Thats not bring back any data but i will have a play around with that approach see if i can get it working thanks

@elenton logically it should work if you have date dimension. May be there is any filter or something which is makign it not to work. Please reach out if you still need help. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 ive got it working now thanks, it is definalty a formatting issue which i will play with to see if i can get a better fix, but for now i just created another column in my date dimension that is just a date field and used today-1 to go back one day which looks to have worked.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.