Forum Discussion

insandur's avatar
insandur
Helper II
3 years ago

SUM DAX Query on condition

I have a table with shipment number and dates. Need to calculate the Total backlog of that shipment and consider the latest date value and sum the column.

 

ShipmentDateBacklog          Result(required)                 
AX28/10/2022280000
AX29/10/202200
BX28/10/202214000
BX29/10/202200

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi insandur ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create 2 measures.

    Measure =
CALCULATE (
    SUM ( 'Table'[Backlog] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Shipment] = SELECTEDVALUE ( 'Table'[Shipment] )
    )
)

    Measure2 =
VAR _maxdate =
    MAXX ( ALL ( 'Table' ), 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Backlog] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _maxdate )
    )

     

    If I have misunderstood your meaning, please provide more details with your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • insandur's avatar
      insandur
      Helper II
      3 years ago

      Hi Polly,

       

      Thanks for the solution, Backlog in my table is actually a meausre not a column so i am not able to get the correct ans.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi insandur ,

        I have modified the answer. Please refer to it to see if it helps you.

        Measure = SUMX(FILTER(ALL('Table'),'Table'[Shipment]=SELECTEDVALUE('Table'[Shipment])),[Mbacklog])
        Measure2 = 
VAR _maxdate =
    MAXX ( ALL ( 'Table' ), 'Table'[Date] )
RETURN
    SUMX(
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _maxdate )
    ,[Mbacklog])

        The [Mbacklog] is also a measure.

         

         

        Best Regards

        Community Support Team _ Polly

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi insandur ,

    Does that make sense? If so, kindly mark my answer as the solution to close the case please. If it does not , please share your ways. Thanks in advance.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.