Add condition to calculated column based on filter applied

[rax99]

Consider the following table:

Table1

Customer	Date	NDayNo.	CallID	AgentHandledFlag
A	23/09/2018	0	154987	0
A	24/09/2018	1	154988	0
A	24/09/2018	1	154989	1
B	01/09/2018	0	154990	0
B	27/09/2018	26	154991	1
C	14/09/2018	0	154992	0
C	26/09/2018	12	154993	1
C	29/09/2018	15	154994	1
D	29/09/2018	0	154995	0
D	30/09/2018	1	154996	1

NDayNo. = Normalised day

So 0 = first time they contacted in the month.

eg Customer A first called on 23rd (=0) then again on the 4th(NdayNo or difference from 0 =1)

     Customer B first called on 1st (=0), then again on the 27th (NdayNo or difference from 0=26 )

     Customer C first called on 14th (=0), then again on the 26th (NdayNo or difference from 0 =12), then again on 29th (NdayNo or difference from 0 =15)

Now, I have added a filter that filters on AgentHandleFlag column (0, and/or 1)

Upon filtering, the resulting table appears like this:

Table2

Customer	Date	NDayNo.	CallID	AgentHandledFlag	**DesiredNDayNo.**
A	24/09/2018	1	154989	1	0
B	27/09/2018	26	154991	1	0
C	26/09/2018	12	154993	1	0
C	29/09/2018	15	154994	1	3
D	30/09/2018	1	154996	1	0

Now that agenthandledflag =0 rows have been removed, I need the NDayNo to re-adjust based on the new result set. So I need a new calc column that adjusts the NDayNo. so that its now based on the filtered results. (See the DesiredNDayNo. column)

eg. Customer A Calls on the 24th for the first time (NDayNo. now = 0), etc.

Is there anyway we can add conditions to calculated columns based on filters applied?

So from Table 1, the condition would be, if AgentHandleFlag = 0 and I change the AgentHandleFlag_filter to 1, then adjust the NdayNo. to be based on the new dataset (which is DesiredNDayNo. in Table 2).

I hope I have made this clear, but let me know if further clarification is required. Thanks

[v-yulgu-msft]

Hi @rax99,

Please try this measure:

previous date =
IF (
    SELECTEDVALUE ( Table2[Date] )
        = CALCULATE (
            MIN ( Table2[Date] ),
            FILTER (
                ALLSELECTED ( Table2 ),
                Table2[Customer] = SELECTEDVALUE ( Table2[Customer] )
                    && Table2[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( Table2[Date] ) )
            )
        ),
    0,
    DATEDIFF (
        CALCULATE (
            MIN ( Table2[Date] ),
            FILTER (
                ALLSELECTED ( Table2 ),
                Table2[Customer] = SELECTEDVALUE ( Table2[Customer] )
                    && Table2[Year-Month] = SELECTEDVALUE ( Table2[Year-Month] )
                    && Table2[Date] < MAX ( Table2[Date] )
            )
        ),
        MAX ( Table2[Date] ),
        DAY
    )
)

Best regards,

Yuliana Gu

[LivioLanzo]

If you wish to do it via calculated column then try like this:

NDayNo Flag Based =
MAX (
    0,
    CALCULATE (
        MIN ( 'Table'[Date] ),
        ALLEXCEPT (
            'Table',
            'Table'[Customer],
            'Table'[AgentHandledFlag]
        ),
        'Table'[Date] > EARLIER ( 'Table'[Date] )
    ) - 'Table'[Date]
)