Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I have some sales data with date/time as per below sample:
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
Solved! Go to Solution.
@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))
@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))
@ 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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |