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
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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, 

HI @Maghol

 

Did you see the attached file??

 

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


Regards
Zubair

Please try my custom visuals

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

@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

 


Regards
Zubair

Please try my custom visuals

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

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

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

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

 

 


Regards
Zubair

Please try my custom visuals

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

@Maghol

 

I will get back to you


Regards
Zubair

Please try my custom visuals

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.