How to create Incident created and closed metrics based on Created and closed dates

[harishkanyal]

Hi,

Need help in creating Incident closed and created IN based on closed and created dates.

I have craeted 

Incident data 

IN	Created on	Closed on
IN001	10-Jun-19	10-Jun-19
IN002	10-Jun-19	11-Jun-19
IN003	09-Jun-19	10-Jun-19
IN004	10-Jul-19	11-Jul-19
IN005	10-Jul-19	10-Jul-19
IN006	10-Jul-19	10-Jul-19
IN007	11-Jul-19	12-Jul-19
IN008	10-Jun-19	01-Jul-19
IN009	11-Jul-19	11-Jul-19
IN010	12-Jul-19	13-Jul-19

Report

	Opened	Closed
June	4	3
July	6	7

Thanks

Harish

[v-diye-msft]

Hi @harishkanyal 

You can add a calculated table and manage the relationship between the fact table, note there’re 2 relationships. [Closed on]with[Date] is active, [Created on] with [Date] is inactive.

Table = CALENDAR(DATE(2019,6,1),DATE(2019,7,31))

Then add the calculated column and measures:

Month = MONTH('Table'[Date])

NrofCl = CALCULATE(DISTINCTCOUNT('Incident data'[IN]),VALUES('Table'[Month]))
NrofCR = CALCULATE(DISTINCTCOUNT('Incident data'[IN]),USERELATIONSHIP('Incident data'[Created on],'Table'[Date]),ALLEXCEPT('Table','Table'[Month]))

Results shown as:

Best regards,

Dina Ye

[harishkanyal]

Thanks @v-diye-msft  . Let me try it

[v-diye-msft]

Hi @harishkanyal ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye

[harishkanyal]

Hi Dina,

I tried the solution but not getting right output for created on.

Could you please help. I uploaded my file in one drive Linkhttps://microsoft-my.sharepoint.com/:f:/g/personal/v-hcsing_microsoft_com1/EvLOOMTwMV1NhQhj_d-2BIUBx...

Thanks

Harish

[v-diye-msft · Accepted solution]

Hi @harishkanyal ,

Attached the file here: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ETgYiqiwf5xHrYrApGa01X...

NrofCl&NrofCR are measures, not calculated column.

Best regards,

Dina Ye

[harishkanyal]

Thanks Dina, this worked