Statistics based on predefined period in another table

Question

Hi,I need to create&nbsp;incidents statistics occurred in a predefined period for :-i) Incident count by periodii) Backlog&nbsp;by periodiii) Backlog completed by period&nbsp;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&nbsp;PeriodTableDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StartDate&nbsp; &nbsp; &nbsp; EndDate&nbsp; &nbsp; &nbsp; Period22/7/2017 &nbsp; &nbsp; &nbsp; &nbsp;22/7/2017&nbsp; &nbsp; 19/8/2017&nbsp; &nbsp;22/07/2017 - 19/08/201721/7/2017&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 22/7/2017&nbsp; &nbsp; 19/8/2017&nbsp; &nbsp;22/07/2017 - 19/08/2017::19/8/2017&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;22/7/2017&nbsp; &nbsp; 19/8/2017&nbsp; &nbsp;22/07/2017 - 19/08/2017&nbsp;20/8/2017&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;19/8/2017&nbsp; &nbsp; 16/9/2017&nbsp; &nbsp;19/08/2017 - 16/09/201721/8/2017&nbsp; &nbsp; &nbsp; &nbsp; 19/8/2017&nbsp; &nbsp;&nbsp;16/9/2017 &nbsp; 19/08/2017 - 16/09/2017::19/8/2017&nbsp; &nbsp; &nbsp; &nbsp; 19/8/2017&nbsp; &nbsp;&nbsp;16/9/2017 &nbsp; 19/08/2017 - 16/09/2017&nbsp;&nbsp;IncidentInc#&nbsp; &nbsp; RaisedDate&nbsp; &nbsp;Closed Date1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21/7/2017&nbsp; &nbsp;&nbsp;24/7/20172&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27/7/2017&nbsp; &nbsp;&nbsp;4/8/20173&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27/7/2017&nbsp; &nbsp; 15/8/20174&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28/7/2017&nbsp; &nbsp; 7/9/20175&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/8/2017&nbsp; &nbsp; &nbsp; 14/9/20176&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/8/2017&nbsp; &nbsp; &nbsp; 15/9/20177&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21/8/2017&nbsp; &nbsp; &nbsp;1/9/20178&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/9/2017&nbsp; &nbsp; 14/9/2017&nbsp;From the sample date, I am expecting results as below:&nbsp;&nbsp;Period Start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IncidentCount&nbsp; &nbsp; &nbsp; Backlog&nbsp; &nbsp;BacklogCompleted22/07/2017 - 19/08/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;019/08/2017 - 16/09/2017&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;--&gt; Backlog and BacklogCompleted count for&nbsp;19/08/2017 - 16/09/2017 is 3 because Inc#4, 5 and 6 closed within this period&nbsp;&nbsp;I have the Incident table and have tried the 2 different formulas attached below but results are not showing.&nbsp;&nbsp;&nbsp;&nbsp;Results are not showing:&nbsp;&nbsp;Have I missed out something here?&nbsp; Any advice is very much appreciated!Thanks!&nbsp;

aztec · Answer

Please help...

v-chuncz-msft · Answer

Aztec,
&nbsp;
You may refer to the following measures.
IncidentCount =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] &lt; SELECTEDVALUE ( PeriodTable[EndDate] )
            &amp;&amp; Incident[Closed Date] &gt;= SELECTEDVALUE ( PeriodTable[StartDate] )
    )
)

Backlog =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] &lt; SELECTEDVALUE ( PeriodTable[StartDate] )
            &amp;&amp; Incident[Closed Date] &gt;= SELECTEDVALUE ( PeriodTable[StartDate] )
    )
)

BacklogCompleted =
COUNTROWS (
    FILTER (
        Incident,
        Incident[RaisedDate] &lt; SELECTEDVALUE ( PeriodTable[StartDate] )
            &amp;&amp; Incident[Closed Date] &gt;= SELECTEDVALUE ( PeriodTable[StartDate] )
            &amp;&amp; Incident[Closed Date] &lt; SELECTEDVALUE ( PeriodTable[EndDate] )
    )
)

aztec · Answer

v-chuncz-msft&nbsp;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&nbsp;Can you please advice why is it happenning this way?Appreciate your help! Help for anyone very much appreciated!&nbsp;&nbsp;

Forum Discussion

Statistics based on predefined period in another table

3 Replies

Related content

Activity Statistics

Predefined Spark resource profiles

Predefined Filter Sets

Usage Statistics for Mobile Users

Conditional font colour formatting based on predefined values from another table