Forum Discussion

nandvroe's avatar
nandvroe
Frequent Visitor
8 years ago
Solved

Assistance with Dax Formula

I am in need of some assistance with a Dax formula.  I have tried several variations of the formula below:

 

TotalPending = CALCULATE(SUM(SalesOrderLines[Net amount]), (SalesOrderLines[Line status])<>"Invoiced", ALLEXCEPT(SalesOrderLines,SalesOrderLines[Customer]),FILTER( ALL(SalesOrderLines[Created date]),[Created date]<=[LastD]))) 

 

but am unable to get the entire formula to work.  

 

 

 

1.  I am attempting to sum (SalesOrderLines[Net amount] 

2.  for only (SalesOrderLines[Line Status] <> "Invoiced"  

 

and do the following

3.  Ignore filter by the Date Slicer, but instead filter dates <= [LastD] 

4.  Ignore filter by the Customer Slicer 

 

LastD=LastDate(DateTbl[Date])

 

I can get parts 1-3 above to work with the following formula:

 

TotalPending = CALCULATE(SUM(SalesOrderLines[Net amount]),SalesOrderLines[Line status]<>"Invoiced",FILTER( ALL(SalesOrderLines[Created date]),[Created date]<=[LastD]))

 

I'm struggling to wrap my head around "All" and "AllExcept".  I'm stating the obvious when I say I've very new to DAX, :smileylol: so any help is greatly appreciated.

 

Thank you

Need Help
  • nandvroe's avatar
    nandvroe
    8 years ago

    Zubair_Muhammad

     

    Thanks to your help, I was able to get the Dax syntax that I needed.  The formula that seems to be working is:

     

    TotalPending2 =
    CALCULATE (
    SUM ( SalesOrderLines[Net amount] ),
    SalesOrderLines[Line status] <> "Invoiced",
    ALLEXCEPT (SalesOrderLines,SalesOrderLines[Customer]), FILTER ( ALL ( SalesOrderLines[Created date] ), [Created date] <= [LastD] )
    )

     

    Again, I'm finding the All and the AllExcept a little confusing.  Thank you again for your help.

     

     

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    nandvroe

     

    Give this a try...Just removing filters from Customer column using ALL

     

    TotalPending =
CALCULATE (
    SUM ( SalesOrderLines[Net amount] ),
    SalesOrderLines[Line status] <> "Invoiced",
    FILTER ( ALL ( SalesOrderLines[Created date] ), [Created date] <= [LastD] ),
    ALL ( SalesOrderLines[Customer] )
)
    • nandvroe's avatar
      nandvroe
      Frequent Visitor
      8 years ago

      Zubair,

      Thank you.  However the formula still continues to respond to the Customer filter (slicer).  

       

       

      • nandvroe's avatar
        nandvroe
        Frequent Visitor
        8 years ago

        Zubair_Muhammad

         

        Thanks to your help, I was able to get the Dax syntax that I needed.  The formula that seems to be working is:

         

        TotalPending2 =
        CALCULATE (
        SUM ( SalesOrderLines[Net amount] ),
        SalesOrderLines[Line status] <> "Invoiced",
        ALLEXCEPT (SalesOrderLines,SalesOrderLines[Customer]), FILTER ( ALL ( SalesOrderLines[Created date] ), [Created date] <= [LastD] )
        )

         

        Again, I'm finding the All and the AllExcept a little confusing.  Thank you again for your help.