cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martin_alt Helper II
Helper II

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

Hi guys,

 

I have a table that looks like that :

 

SERIAL NUMBERTYPEDATE INDATE OUT
800A01/01/201703/01/2017
900B04/01/201706/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 :

 

DATETYPECOUNT
01/01/2017A1
01/01/2017B0
02/02/2017A1
02/02/2017B0
03/03/2017A1
03/03/2017B0
04/04/2017A0
04/04/2017B1
05/05/2017A0
05/05/2017B1
06/06/2017A0
06/06/2017B1

 

Would you have a solution ? 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

Hi @martin_alt,


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]) }
1.PNG

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

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])
3.PNG

 
Regards,
Lydia Zhang

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

View solution in original post

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

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

Hi @martin_alt,


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]) }
1.PNG

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

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])
3.PNG

 
Regards,
Lydia Zhang

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

View solution in original post

martin_alt Helper II
Helper II

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

Thanks it does work 🙂

martin_alt Helper II
Helper II

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

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

 

PIC122;PIC099;PIC156

 

 

Thanks 🙂

Moderator v-yuezhe-msft
Moderator

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

Hi @martin_alt,


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

Thanks,
Lydia Zhang

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors