calculating difference based on multiple filters

[Anonymous]

Ok resorting to posting on here for help. I am trying to find the difference between how many orders have left a state and how many have came into a state. Below is how my data is set up:

destination state            date delivered                   date shipped           order id             shipper state          orders

TX                                  1/1/2019                             1/1/2019                 12315                  IL                           1

AL                                  1/1/2019                             1/1/2019                 12642                   NY                       1

[Anonymous]

Hi @Anonymous ,

You can try to use following measure formula to calculate leave and arrive count based current date and state:

leave count =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        COUNT ( Table[ID] ),
        FILTER (
            ALLSELECTED ( Table ),
            currDate IN CALENDAR ( Table[Date delivered], Table[date shipped] )
                && [Date delivered] <> currDate
                && [date shipped] <> currDate
        ),
        VALUES ( Table[shipper state] )
    )


arrived count =
VAR currDate =
    MAX ( calendar[Date] )
RETURN
    CALCULATE (
        COUNT ( Table[ID] ),
        FILTER ( ALLSELECTED ( Table ), [date shipped] = currDate ),
        VALUES ( Table[destination state] )
    )

If above not help, can you please explain more about your requirement?

Regards,

Xiaoxin Sheng

[Anonymous]

No this didn't help. My ultimate goal is to take the variance of two visuals. One visual has all the shipments leaving a state and the other has all the shipments coming into a state. I need a 3rd visual that takes the difference of these.

[Anonymous]

Hi @Anonymous ,

You can write a measure with variable summarize table(state, leave count, coming count), then use iteration function VARX.P/VARX.S on summary table to calculate variance.

Regards,

Xiaoxin Sheng