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

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

        HI Maghol

         

        Did you see the attached file??

         

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