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

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

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