Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a flat table and want to view orders at a specific moment in time
order_id qty log_date 1 3 2018-03-03 1 2 2018-01-06 1 4 2017-12-04 1 6 2017-10-10 2 1 2018-02-01 2 3 2018-01-04 2 2 2018-01-02 2 4 2017-12-01
Expected result would be id=1, qty=4 and id=2, qty=3
How can I convert following SQL to DAX?
declare @selectedDate date;
set @selectedDate = cast('2018-01-05' as datetime);
select
rc1.order_id
, qty
from [dbo].[Orders] rc1
where
rc1.Logdate <= @selectedDate
and rc1.Logdate = (select MAX(rc2.logdate) from Orders rc2 where rc2.order_id = rc1.order_id)
Regards
Solved! Go to Solution.
HI @Maghol
Please try this one
TEST = VAR TheDate = DATE ( 2018, 12, 5 ) RETURN SUMMARIZE ( Blad1, Blad1[order_id], "The_Qty", CALCULATE ( SUM ( Blad1[qty] ), TOPN ( 1, FILTER ( VALUES ( Blad1[log_date] ), Blad1[log_date] <= TheDate ), [log_date], DESC ) ) )
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |