Including Widgets based on 2 parameters based on year

[danb]

Hello Experts, 

I have an issue that I am a bit at a loss on how to solve. I have a table with sales data in it by month. The table will have anywhere between 13 and 24 months depending on today's date. What I am looking to do is identify which widgets meet the following criteria:

1. Had sales in all 12 months in 2018

2. Have sales in all complete months so far in 2019 

What I am trying to do is identify which widgets meet these two criteria and then I will include them in an additional analysis and suppress everything that does not meet both criteria. I have included a table as a mock up for you to play around with. 

The answer should be only "A" and "F" should be included as meeting both criteria. 

Thank you in advance! 

Widget	Date	Quantity
A	1/1/2018	1
A	2/1/2018	3
A	3/1/2018	5
A	4/1/2018	5
A	5/1/2018	6
A	6/1/2018	4
A	7/1/2018	1
A	8/1/2018	4
A	9/1/2018	9
A	10/1/2018	9
A	11/1/2018	4
A	12/1/2018	10
A	1/1/2019	10
A	2/1/2019	10
A	3/1/2019	6
A	4/1/2019	2
B	1/1/2019	10
B	2/1/2019	6
B	3/1/2019	5
B	4/1/2019	4
C	1/1/2018	1
C	2/1/2018	6
C	3/1/2018	2
C	4/1/2018	6
C	5/1/2018	3
C	6/1/2018	9
C	7/1/2018	2
C	8/1/2018	8
C	9/1/2018	8
C	10/1/2018	9
C	11/1/2018	5
C	12/1/2018	3
D	2/1/2018	4
D	3/1/2018	5
D	4/1/2018	2
D	5/1/2018	3
D	6/1/2018	1
D	7/1/2018	3
D	8/1/2018	2
D	9/1/2018	5
D	10/1/2018	1
D	11/1/2018	1
D	12/1/2018	3
D	1/1/2019	10
D	2/1/2019	4
D	3/1/2019	10
D	4/1/2019	5
E	1/1/2018	3
E	2/1/2018	5
E	3/1/2018	5
E	4/1/2018	2
E	5/1/2018	5
E	6/1/2018	10
E	7/1/2018	7
E	8/1/2018	9
E	9/1/2018	9
E	10/1/2018	6
E	11/1/2018	9
E	12/1/2018	9
E	1/1/2019	6
E	2/1/2019	10
F	1/1/2018	1
F	2/1/2018	4
F	3/1/2018	3
F	4/1/2018	6
F	5/1/2018	5
F	6/1/2018	2
F	7/1/2018	7
F	8/1/2018	9
F	9/1/2018	10
F	10/1/2018	3
F	11/1/2018	5
F	12/1/2018	6
F	1/1/2019	9
F	2/1/2019	8
F	3/1/2019	1
F	4/1/2019	9

[v-lili6-msft · Accepted solution]

hi, @danb 

You could try this way:

Step1:

Add a year column and a month column for Date

Step2:

Use this formula to create a measure

Measure = 
IF (
    CALCULATE ( COUNTA ( Table1[Month] ), FILTER ( Table1, Table1[Year] = 2018 ) ) = 12
        && CALCULATE (
            COUNTA ( Table1[Month] ),
            FILTER ( Table1, Table1[Year] = 2019 && Table1[Month] < MONTH ( TODAY () ) )
        )
            = MONTH ( TODAY () ) - 1,
    1
)

Step3:

Drag Widget column and this measure into a table visual

or just drag Widget column  into a table visual and drag this measure into  visual level filter and set is not blank.

Result:

here is pbix file, please try it.

Best Regards,

Lin

[danb]

@v-lili6-msft - this works perfectly! Many thanks!

Dan