Visualize work in progress(WIP) at any given point of time in Power BI

It is a very common requirement to visualize the WIP or sales at any given point of time in Power BI. This task sound really simple but it needs extensive DAX effort to summaries this details.

A simple example.

Incident No	Incident Open Date	Incident Closed Date	2018 Jan	2018 Feb	2018 Mar
1ABC	01/01/2018	02/02/2018	1	1	0
2ABC	01/01/2018	30/01/2018	1	0	0
3ABC	15/01/2018	15/03/2018	1	1	1
4ABC	06/02/2018		0	1	1
Total			3	3	2

To do this task, first create the DATE table which has Year and Month columns.

In your data table you use to measure the WIP create a measure like below:

Measure =

 CALCULATE(

DISTINCTCOUNT(Workload[INCIDENTNO]),

GENERATE(VALUES(Dates[TIMELINE_DATE]),

FILTER(Workload,CONTAINS(

    DATESBETWEEN(Dates[TIMELINE_DATE],Workload[CREATED_DATE],Workload[CLOSED_DATE]),

                Dates[TIMELINE_DATE],Dates[TIMELINE_DATE])

)

)

)

****Most importantly this measure works only when your Workload table has NO relationship with Dates table. ****

DAX commands used in above measure are: 

CALCULATE(<expression>,<filter1>,<filter2>…)

DISTINCTCOUNT(<column>)

GENERATE(<table1>, <table2>)

VALUES(<TableNameOrColumnName>)

FILTER(<table>,<filter>)

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

DATESBETWEEN(<dates>,<start_date>,<end_date>)