cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Weatherreport
Frequent Visitor

DAX Measure returning previous week sales by time range

Hi Everyone,

 

I have some sales data with date/time as per below sample:

 

Weatherreport_0-1615869160200.png

 

I would like to build a DAX measure that would return previous week (to date) sales between 4am and NOW.

In other words, as our business week starts on Tuesday and ends on Monday, let's say that if today is Wednesday 17/03 3:39pm, I would need a formula that returns sales between Tue 9/03 4am and Wed 10/03 3:39 pm.

 

I hope it makes sense but happy to provide a better explanation if required.

 

Thank you so much for helping.

 

Regards,

Marco

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Weatherreport , Last tueday will be

 

Last Tuesday = (Today() +-1*WEEKDAY(Today(),2)+1) -6

 

So you can trya measure like

 

From last tuesday =
var _last =(Today() +-1*WEEKDAY(Today(),2)+1) -6 + time(hour(now()), minute(now()), second(Now()))
var _now = now()
return
calculate(sum(Table[Value]), filter(Table, Table[Datetime] >=_last && Table[Datetime] <=_now))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Weatherreport
Frequent Visitor

@amitchandak Sorry maybe I hadn't tagged you properly before

amitchandak
Super User IV
Super User IV

@Weatherreport , Last tueday will be

 

Last Tuesday = (Today() +-1*WEEKDAY(Today(),2)+1) -6

 

So you can trya measure like

 

From last tuesday =
var _last =(Today() +-1*WEEKDAY(Today(),2)+1) -6 + time(hour(now()), minute(now()), second(Now()))
var _now = now()
return
calculate(sum(Table[Value]), filter(Table, Table[Datetime] >=_last && Table[Datetime] <=_now))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@ amitchandak

Thanks for your prompt reply. The formula seems working pretty well except there is some small difference in terms of amount and I must understand why. 

However, as it is at least a good step towards the solution, I'm happy to accept it as a solution.

 

Quick question (otherwise I might raise another ticket to the Community): If I only want last week sales for same time as today (eg. between midnight and now) - instead than the WTD as per above formula - what should I change it to make it working?

 

Thanks,

Marco

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!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates