Count the number of "in progress" products with an IN and a OUT date

[Anonymous]

Hi guys,

I have a table that looks like that :

SERIAL NUMBER	TYPE	DATE IN	DATE OUT
800	A	01/01/2017	03/01/2017
900	B	04/01/2017	06/01/2017

I would like to create a table that counts the number of products by type who are in "in progress" for each date, for example in this case :

DATE	TYPE	COUNT
01/01/2017	A	1
01/01/2017	B	0
02/02/2017	A	1
02/02/2017	B	0
03/03/2017	A	1
03/03/2017	B	0
04/04/2017	A	0
04/04/2017	B	1
05/05/2017	A	0
05/05/2017	B	1
06/06/2017	A	0
06/06/2017	B	1

Would you have a solution ? 🙂

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

Firstly, in Query Editor of Power BI Desktop, add a custom column named Alldays with the formula below.

{ Number.From([DATE IN])..Number.From([DATE OUT]) }

Secondly, expand the custom column and format its type to “Date”.

Thirdly, create a measure using DAX below and create Matrix visual using the measure and Alldays column, then use Alldays slicer to filter the Matrix visual. The Matrix visual will display the closest format as your desired result.

COUNT = COUNT(TEST[TYPE])

 
Regards,
Lydia Zhang

[Anonymous]

Thanks it does work 🙂

[Anonymous]

Would it be possible to use the same method with textcodes separated by a semicolon like this :

PIC122;PIC099;PIC156

Thanks 🙂

[v-yuezhe-msft]

Hi @Anonymous,

Please open a new thread about the above issue since the thread has been closed.

Thanks,
Lydia Zhang