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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maghol
Frequent Visitor

SQL to DAX

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

1 ACCEPTED 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
            )
        )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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