SQL to DAX

[Maghol]

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

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@Maghol

See File attached here with sample data

 

[Maghol]

Thanks for the reply but I get an error: "A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported".

Log_date is in dateformat, 

[Zubair_Muhammad]

HI @Maghol

Did you see the attached file??

Did you use the formula as a Calculated TABLE???

[Maghol]

No, I tried it as calculated table on my on data. Problem is that there are many entries with the same date for one order_id in log_date, only timestamp seperates them. Think I need to use MAX() instead of LASTDATE()?

[Zubair_Muhammad]

@Maghol

It shouldn't make a difference...I think

It will sum up the values of the lastdate that meets the criteria

I duplicated the date and it worked

You can share you file if you like

[Maghol]

I have tried following DAX with the sample data in the attached Excel-file. Same error:
"A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported."

TEST = 
VAR TheDate = DATE ( 2018 , 1 , 5 )
RETURN
    SUMMARIZE (
        Blad1,
        Blad1[order_id],
        "The_Qty", CALCULATE (
            SUM ( Blad1[qty] ),
            LASTDATE (
                FILTER (
                    VALUES ( Blad1[log_date] ),
                    Blad1[log_date] <= TheDate
                )
            )
        )
    )

Example data here

[Zubair_Muhammad · 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
            )
        )
    )

[Maghol]

How can I change the static datevalue in the variable to a selected value from a slicer? Tried using the columnvalue directly, SELECTEDVALUE('slicer'), MAX(), ALLSELECTED(), IFHASONEVALUE() but the selected slicervalue isn't assigned to the variable.

Regards

[Zubair_Muhammad]

Hi @Maghol

Unlike measures, calculated column and Calculated tables are computed during database processing(e.g. data refresh) and then stored in the model, so their calculation doesnot change when you select a slicer value

[Maghol]

Ok, thanks for the reply.
Is there a way to accomplish this? 

[Zubair_Muhammad]

@Maghol

I will get back to you