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 ...
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
            )
        )
    )
Maghol's avatar
Maghol
Frequent Visitor
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
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
            )
        )
    )
  • Maghol's avatar
    Maghol
    Frequent Visitor
    8 years ago

    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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
    Maghol
    Frequent Visitor
    8 years ago

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