Reply
Member
Posts: 78
Registered: ‎07-25-2018
Accepted Solution

Formula Help

I am looking to do a formula that shows # of shipments yesterday.  I then will have a table where I have a list of carriers and I can see how many shipments each had yesterday.  I created the following measure:

 

Shipments Yesterday = 
        CALCULATE(
            SUM('public vw_shipment_quality_assurance'[LOADS]),
            FILTER(ALLSELECTED('public vw_shipment_quality_assurance'), 'public vw_shipment_quality_assurance'[ROUTE START DATE]
                = TODAY()-1))
                

This measure does calculate the total shipments for yesterday successfully, however when I add it to the table with the carriers, it just lists the total number of shipments next to each carrier.  In other words, every carrier has 3593 next to it, instead of that carrier's total.

Any ideas on how this should work?


Accepted Solutions
Highlighted
Established Member
Posts: 183
Registered: ‎03-22-2018

Re: Formula Help

@schwinnen -

 

Well, knowing that, maybe this will work:

 

Measure 2 =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Carrier], Table1[Customer] ),
        Table1[Date]
            = TODAY () - 1
    )
)

Should yield:

 

2.PNG

View solution in original post


All Replies
Established Member
Posts: 183
Registered: ‎03-22-2018

Re: Formula Help

@schwinnen -

 

Try the following [Measure] :

 

Measure =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Carrier] ), Table1[Date] = TODAY () - 1 )
)

The image shows [Pieces] by date in the top matrix and a matrix with [Carrier], your [Shipments Yesterday] and [Measure]

 

1.PNG

 

Member
Posts: 78
Registered: ‎07-25-2018

Re: Formula Help

Thank you, @ChrisMendoza.  This works, but I have one other problem.  I have a report level filter on that filters out a specific customer and this formula does not seem to be recognizing that filter.  I think maybe this was why I was using ALLSELECTED.  Any ideas on how to get around that?

Highlighted
Established Member
Posts: 183
Registered: ‎03-22-2018

Re: Formula Help

@schwinnen -

 

Well, knowing that, maybe this will work:

 

Measure 2 =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Carrier], Table1[Customer] ),
        Table1[Date]
            = TODAY () - 1
    )
)

Should yield:

 

2.PNG

Member
Posts: 78
Registered: ‎07-25-2018

Re: Formula Help

Thank you so much, @ChrisMendoza.  That seems to work perfectly.  I assume this means that if I add any other visual, page or report level filters I will have to add them to the ALLEXCEPT formula?

Established Member
Posts: 183
Registered: ‎03-22-2018

Re: Formula Help

@schwinnen -

 

I can't say, as I am still learning as well. Here is a link that may help you further your knowledge https://dax.guide/allexcept/. You'll notice they link to articles that further explain as well.

 

Maybe someone else in the community has more detail they can expand on?

 

A suggestion for you though would be provide details that could impact the solution (i.e. the Report Filter) or better yet the dataset(s) you're actually working with.