calculating difference based on multiple filters

Question

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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; date delivered&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date shipped&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;order id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;shipper state&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ordersTX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12315&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1AL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12642&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
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] )
                &amp;&amp; [Date delivered] &lt;&gt; currDate
                &amp;&amp; [date shipped] &lt;&gt; 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 · Answer

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 · Answer

Hi&nbsp;Anonymous&nbsp;,
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

Forum Discussion

calculating difference based on multiple filters

3 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Using Visual Calculation on a Calculated Group with Multiple Calculated Items

CALCULATE with multiple filters

1. How to add two different calculated columns of two different table