Finding distinct count of object under each status for a given startdate and enddate

[chelvys]

I have a table:

Object      Status       StartDate       EndDate

Ob1               A          2018/3/3       2018/4/18

Ob1               B          2018/4/19     2018/8/30

Ob1               S          2018/9/1       2018/10/15

Ob2               S          2018/1/13     2018/3/5

Ob2               A          2018/3/6       2018/10/18

In the report, a date or a week or a month, or a range of dates may be selected and  the chart need to calculate and display, for the given range how many objects are avalible for each stage.

How do i do this?

[AkhilAshok · Accepted solution]

You can do this in multiple ways. Best way to do with minimum DAX code and minimum formula engine involvment is as below:

1. Create a simple Date table (you can enhance it with more columns like year, month, etc if you need).

Date = CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )

2. Create a new Fact table:

Fact = 
SELECTCOLUMNS (
    GENERATE (
        'Table',
        DATESBETWEEN ( 'Date'[Date], 'Table'[Start Date], 'Table'[End Date] )
    ),
    "Object", 'Table'[Object],
    "Status", 'Table'[Status],
    "Date", 'Date'[Date]
)

3. Create a relationship between Date[Date] & Fact[Date]

4. Create the measure:

Unique Object # = DISTINCTCOUNT('Fact'[Object])

5. Now you can create your chart/table, adding Fact[Status] & [Unique Object #] and keeping Date[Date] as the slicer.

Hope this helps.

[chelvys]

I have a table:

Object      Status       StartDate       EndDate

Ob1               A          2018/3/3       2018/4/18

Ob1               B          2018/4/19     2018/8/30

Ob1               S          2018/9/1       2018/10/15

Ob2               S          2018/1/13     2018/3/5

Ob2               A          2018/3/6       2018/10/18

In the report, a date or a week or a month, or a range of dates may be selected and  the chart need to calculate and display, for the given range how many objects are avalible for each stage.

How do i do this?

[LivioLanzo · Accepted solution]

see attached file: https://1drv.ms/u/s!AiiWkkwHZChHjy2AyKpmHxnHYq2U

[chelvys]

Thank you your solutions worked.

[AkhilAshok · Accepted solution]

You can do this in multiple ways. Best way to do with minimum DAX code and minimum formula engine involvment is as below:

1. Create a simple Date table (you can enhance it with more columns like year, month, etc if you need).

Date = CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )

2. Create a new Fact table:

Fact = 
SELECTCOLUMNS (
    GENERATE (
        'Table',
        DATESBETWEEN ( 'Date'[Date], 'Table'[Start Date], 'Table'[End Date] )
    ),
    "Object", 'Table'[Object],
    "Status", 'Table'[Status],
    "Date", 'Date'[Date]
)

3. Create a relationship between Date[Date] & Fact[Date]

4. Create the measure:

Unique Object # = DISTINCTCOUNT('Fact'[Object])

5. Now you can create your chart/table, adding Fact[Status] & [Unique Object #] and keeping Date[Date] as the slicer.

Hope this helps.