Forum Discussion

Maghol's avatar
Maghol
Frequent Visitor
8 years ago
Solved

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

12 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Maghol

     

    Try this calculated Table

     

    From the Modelling Tab>>NEW TABLE

     

    Table =
VAR mydate =
    DATE ( 2018, 1, 5 )
RETURN
    SUMMARIZE (
        Table1,
        Table1[order_id],
        "Qty", CALCULATE (
            SUM ( Table1[qty] ),
            LASTDATE ( FILTER ( VALUES ( Table1[log_date] ), Table1[log_date] <= mydate ) )
        )
    )