Show the missing/added column

[Anonymous]

Hi,

I have a data set as below:

Date	Data
1/1/2019	A
1/1/2019	B
1/1/2019	C
1/1/2019	D
1/1/2019	E
1/1/2019	F
1/1/2019	G
1/1/2019	H
1/1/2019	I
1/1/2019	J
1/2/2019	A
1/2/2019	B
1/2/2019	C
1/2/2019	D
1/2/2019	E
1/2/2019	F
1/2/2019	G
1/2/2019	H
1/2/2019	I
1/2/2019	J
1/2/2019	K
1/2/2019	L
1/2/2019	M
1/2/2019	N
1/2/2019	O
1/3/2019	A
1/3/2019	B
1/3/2019	C
1/3/2019	D
1/3/2019	E
1/3/2019	F
1/3/2019	G
1/3/2019	H
1/3/2019	I
1/3/2019	J
1/3/2019	K
1/3/2019	L
1/3/2019	M
1/3/2019	N
1/3/2019	O
1/3/2019	P
1/3/2019	Q
1/3/2019	R
1/3/2019	S
1/3/2019	T
1/3/2019	U
1/3/2019	V
1/3/2019	W
1/3/2019	X
1/3/2019	Y
1/3/2019	Z

Date 1is having values from A to J... Date 2 is having A to O and Date 3 is having A to Z. Now I want to compare the values of Date 1 and Date 2 and return the values which are not present.

Example when I select Date 2, it should show the values A to O and also it should show the value K to O as this is not present in Date 1. Respectively it should compare with all previous dates and return values. Any headsup!!!! Kindly help!!

[v-lid-msft]

Hi @Anonymous ,

We can create a calculated table, which contain all the possible data value, then use two measures and table visual to archive your requirement.

All = DISTINCT('Table'[Data])

Added = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] < selectedDay )
RETURN
    IF (
        AND (
            NOT CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Added",
        BLANK ()
    )

Missing =
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] < selectedDay )
RETURN
    IF (
        AND (
            CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            NOT CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Miss",
        BLANK ()
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

Thanks for your response. This helps but with few data issues. Posting that here and looking forward from you.

I have the below data: For Date 1, I am having data from A to J: And it is showing in added when i select date 1. Its fine as this is the First date.

For Date 2, i am having data C to O, so when i select Date 2, it  is showing added data as K to O and missing data as A,B.

Basically it compares with the previous day data and returns the values. Its also fine here.

For Date 3, i am having data from A to W, so when i select Date 3, it is showing added data as P to W, but it also should show A,B as these two data is not available on Date 2, here this Fails.

For Date 4, I am having data from A to E, here it compares with the previous day data and returns the missing data as F to W.

It is working for Few dates and doesn't for Few. Please help me to fix this. Thanks in advance and sorry for the delay response.

@v-lid-msft 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for that I misunderstood the previous as all the days before selected day, we can use the following measures to fix it:

Added = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)) )
RETURN
    IF (
        AND (
            NOT CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Added",
        BLANK ()
    )

Missing = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)))
RETURN
    IF (
        AND (
            CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            NOT CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Miss",
        BLANK ()
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot @v-lid-msft . It helps!!