Formula Help

[schwinnen]

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?

[ChrisMendoza]

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

[schwinnen]

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?

[ChrisMendoza · Accepted solution]

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

[schwinnen]

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?

[ChrisMendoza]

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