Create Measure: Show Datesinbetween open and closed date and distinctcount the value

[sree_12]

Hello Team,

I need your help in writing a measure for calculating daily count of items.

Here's the sample data

Item	Opendate	Closeddate
A	7/20/2020	7/22/2020
B	7/20/2020	7/20/2020
C	7/21/2020	7/27/2020

I need to create virtual table like this and get result from this below table

Item	Opendate	Closeddate
A	7/20/2020	7/22/2020
A	7/21/2020	7/22/2020
A	7/22/2020	7/22/2020
B	7/20/2020	7/20/2020
C	7/21/2020	7/27/2020
C	7/22/2020	7/27/2020
C	7/23/2020	7/27/2020
C	7/24/2020	7/27/2020
C	7/25/2020	7/27/2020
C	7/26/2020	7/27/2020
C	7/27/2020	7/27/2020

In the above table i need to add dates for Item until its closed.

So, the measure has to calculate distinct count of items in each opendate.

Resultset or measure value(Distinct count on each opendate)

Opendate	distinctcount of items
7/20/2020	2
7/21/2020	2
7/22/2020	2
7/23/2020	1
7/24/2020	1
7/25/2020	1
7/26/2020	1
7/27/2020	1

Please suggest me DAX measure to achieve this,

Thank you.

[Anonymous]

In DAX, there are no virtual tables. There are tables, calculated tables and table variables. What do you really want then?

[amitchandak]

@sree_12 ,

Check if this file can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[sree_12]

Thank you @amitchandak for your response.

But the count seems to be incorrect in my case.

Item	Opendate	Closeddate	Category	code
A	7/20/2020	7/22/2020	sales	x
A	7/21/2020	7/22/2020	sales	x
A	7/22/2020	7/22/2020	sales	x
B	7/20/2020	7/20/2020	Purchase	y
C	7/21/2020	7/27/2020	purchase	y
C	7/22/2020	7/27/2020	purchase	x
C	7/23/2020	7/27/2020	purchase	x
C	7/24/2020	7/27/2020	purchase	x
C	7/25/2020	7/27/2020	purchase	x
C	7/26/2020	7/27/2020	purchase	y
C	7/27/2020	7/27/2020	purchase	y

But I forgot to mention if "closed date" is null, add date to opendate until the item is closed and also get the distintinct count by including category column while summarizing.

Please let me know your suggestions.!