Complex Date Calculation

[Anonymous]

Hi Community,

I have a following weird case in which I needed some assistance. My "Date" dimension is connected to the StartDate in the table below. My table looks like this : -

WorkID	StartDate	EndDate	Type
1	1/1/2014	1/5/2017	Rejected
2	7/7/2015		Active
3	4/4/2016	5/7/2017	Closed
4	2/9/2017		Active

Now, I need to calculate the total of the "Active Cases" for the past 5 years by month. The tricky part is that all the "Rejected" cases need to be counted as "Active" for the months between StartDate and EndDate. Any workaround for this?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may take a look at the measure in link below.

http://community.powerbi.com/t5/Desktop/Create-date-Parameter-asat-date-to-get-active-customers/m-p/311306#M137927