Forum Discussion
Including Widgets based on 2 parameters based on year
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 |
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
2 Replies
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
