cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## 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, so any help is greatly appreciated.

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Assistance with Dax Formula

@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 3
Super User III

## Re: Assistance with Dax Formula

@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] )
)```
Try my new Power BI game Cross the River
Frequent Visitor

## Re: Assistance with Dax Formula

Zubair,

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

Frequent Visitor

## Re: Assistance with Dax Formula

@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.

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors