Calculate number of cases worked PER year

[itsmk86]

I need to calculate number of cases worked in a year in a Power BI Report. The values are Case start date and Case end date. A case may start in one year but end in another , therefore, the number of cases per year should take it into account.

EX - A case was started in 2019 Nov and closed in Feb 2020, here the department has worked on it in 2019 and also in 2020. So, in the total number of cases per year 1 case should counted in 2019 and same in 2020.

Any help/reference for DAX is appreciated ?

[Greg_Deckler]

Open Tickets was designed to account for date intervals: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ashish_Mathur]

Hi,

This should be fairly easy to do.  Share some data to work with.  Share it in a format that can be pasted in an Excel file.

[itsmk86]

Here you go.. Sorry , there is no easy way to copy paste/attach files here...

01/21/20 02/14/20 10
01/09/20 01/29/20 11
01/06/20 02/03/20 12
01/06/20 02/03/20 13
12/31/19 12/31/19 14
12/24/19 01/24/20 15
11/20/19 01/16/20 16
11/19/19 11/19/19 17
11/18/19 01/23/20 18
11/12/19 02/18/20 19
11/07/19 02/03/20 20
11/07/19 02/03/20 21
11/06/19 11/13/19 22
11/06/19 01/28/20 23
11/04/19 02/03/20 24
10/29/19 25
10/28/19 02/03/20 26
10/24/19 01/23/20 27
10/21/19 02/12/20 28
10/15/19 10/30/19 29
10/14/19 01/16/20 30
10/11/19 10/23/19 31
10/08/19 12/19/19 32

[Ashish_Mathur]

Hi,

What do those numbers in the third column represent?  Is that an input column or is that the result you are expecting?

[itsmk86]

oops, it deleted quite a bit of content.. I apologize.. Anyways, 

CaseStartdt, CaseEndDt, Case#(UniqueField) are the 3 columns.

Note that some of the CaseEndDt fields are empty therefore the Case# is shifted to the left in the original data I pasted in last post ... 

To give one example accurately, 

CaseStartdt , CaseEnddt, Case#

12/24/19,     01/24/20,     15

11/19/19,    11/19/19,    17

10/21/19,     02/12/20 .    28

[v-lid-msft · Accepted solution]

Hi @itsmk86 ,

We can use the following steps to meet your requirement:

1. Create a date table. Then create a column to calculate the date year.

date table = CALENDAR("2019/1/1","2021/12/1")

year = YEAR('date table'[Date])

2. Then we can create a measure,

count =
var a = SELECTEDVALUE('date table'[year])
return
CALCULATE (
    DISTINCTCOUNT('Table'[Case]),
    FILTER (
        'Table',
        IF(ISBLANK('Table'[CaseEnddt]),YEAR('Table'[CaseStartdt]<= a),YEAR('Table'[CaseStartdt]) <= a && YEAR('Table'[CaseEnddt]) >=a)
    )
)

Put the measure and date table[year] in a column chart.

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

[itsmk86]

Thank you very much, Everone ! 

And a special thanks to V-Lid-msft !