Statistics based on predefined period in another table

[Aztec]

Hi,

I need to create incidents statistics occurred in a predefined period for :-

i) Incident count by period

ii) Backlog by period

iii) Backlog completed by period

So, there is a Period table which indicates the periods with Start and End dates for all dates in the calendar.

And the facts are in "INCIDENT" table below

PeriodTable

Date                 StartDate      EndDate      Period

22/7/2017        22/7/2017    19/8/2017   22/07/2017 - 19/08/2017

21/7/2017         22/7/2017    19/8/2017   22/07/2017 - 19/08/2017

:

:

19/8/2017        22/7/2017    19/8/2017   22/07/2017 - 19/08/2017

20/8/2017        19/8/2017    16/9/2017   19/08/2017 - 16/09/2017

21/8/2017        19/8/2017    16/9/2017   19/08/2017 - 16/09/2017

:

:

19/8/2017        19/8/2017    16/9/2017   19/08/2017 - 16/09/2017

Incident

Inc#    RaisedDate   Closed Date

1         21/7/2017    24/7/2017

2         27/7/2017    4/8/2017

3         27/7/2017    15/8/2017

4         28/7/2017    7/9/2017

5         1/8/2017      14/9/2017

6         2/8/2017      15/9/2017

7         21/8/2017     1/9/2017

8         10/9/2017    14/9/2017

From the sample date, I am expecting results as below:

Period Start                                IncidentCount      Backlog   BacklogCompleted

22/07/2017 - 19/08/2017              6                             0               0

19/08/2017 - 16/09/2017              2                             3               3

--> Backlog and BacklogCompleted count for 19/08/2017 - 16/09/2017 is 3 because Inc#4, 5 and 6 closed within this period

I have the Incident table and have tried the 2 different formulas attached below but results are not showing.

Results are not showing:

Have I missed out something here?  Any advice is very much appreciated!

Thanks!

[v-chuncz-msft]

@Aztec,

You may refer to the following measures.

IncidentCount =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] < SELECTEDVALUE ( PeriodTable[EndDate] )
            && Incident[Closed Date] >= SELECTEDVALUE ( PeriodTable[StartDate] )
    )
)

Backlog =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] < SELECTEDVALUE ( PeriodTable[StartDate] )
            && Incident[Closed Date] >= SELECTEDVALUE ( PeriodTable[StartDate] )
    )
)

BacklogCompleted =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] < SELECTEDVALUE ( PeriodTable[StartDate] )
            && Incident[Closed Date] >= SELECTEDVALUE ( PeriodTable[StartDate] )
            && Incident[Closed Date] < SELECTEDVALUE ( PeriodTable[EndDate] )
    )
)

[Aztec]

@v-chuncz-msft

Thanks for your reply but the 3 formulas all return blank. There is no values after I selected a filter.

I have uploaded a the file to share with you.

https://www.dropbox.com/home/PowerBI?preview=Count_DateRange.pbix

Can you please advice why is it happenning this way?

Appreciate your help! Help for anyone very much appreciated!

[Aztec]

Please help...