Considering Records within Opened and Closed dates

[seira88]

Hi, 

Need suggestions on how to resolve the logic issue I have right now. 

Expected Outcome:

Data:
ID	Date Opened	Date Closed
1	Jul-17
2	Jul-17	Aug-17
3	Aug-17	Aug-17
4	Sep-17
5	Oct-17	Oct-17
	Jul-17	Aug-17	Sep-17	Oct-17
NEW	2	1	1	1
OPEN	2	1	2	2
NFA		1		1

Logic:
Data is counted as New if it is opened within the month
Data is counted as Open if it is not closed within the month (data opened on a different month is counted as well)
Data is counted as NFA if it is closed within the month

Current Power BI Behaviour:

	Jul-17	Aug-17	Sep-17	Oct-17
NEW	2	1	1	1
OPEN	1	1	2	2
NFA		1		1

Analysis: Power BI doesn't consider/count data with Closed Date as Open.

Data:
I have Data Table (Table1) and a Date Table (New Table created in Power BI).

Logic in counting Open Data -

New Column:
IsOpen = IF(ISBLANK('Table1'[closeddate]) &&
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)

New Quick Measure (Running Total):
OPEN =
CALCULATE(
SUM('Table1'[IsOpen]),
FILTER(
CALCULATETABLE(
SUMMARIZE('Date', 'Date'[MonthYearInt], 'Date'[MonthYear]),
ALLSELECTED('Date')
),
ISONORAFTER(
'Date'[MonthYearInt], MAX('Date'[MonthYearInt]), DESC,
'Date'[MonthYear], MAX('Date'[MonthYear]), DESC
)
)
)

Thank you in advance!

[ImkeF]

Maybe using OR instead of AND in the condtion would work:

old:

IsOpen = IF(ISBLANK('Table1'[closeddate]) &&
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)

new:

IsOpen = IF(ISBLANK('Table1'[closeddate]) ||
'Table1'[OpenedClosed_SameMonth] <> 1, 1,0)

[seira88]

Hi, I tried "||" but it gives me this result.

	Jul-17	Aug-17	Sep-17	Oct-17
NEW	2	1	1	1
OPEN	2	3	5	5
NFA		1		1

the succeeding month mismatch with the expected output.

[Anonymous]

@seira88,

What field of table1 do you use to create relationship between table1 and date table? Do you get expected result when you directly drag isOpen column to your visual?

Also please share us your Date table.

Regards,
Lydia

[seira88]

Hi Lydia,

I created DateAsInt field in both Table1 and Date tables and that is what I used to link them. 

As per IsOpen, I cannot directly use it as I won't see the  value of previous months wherein thereis no data created. (see example below)

Data:
ID	Date Opened	Date Closed
1	Jul-17
2	Jul-17	Aug-17
3	Aug-17	Aug-17
4	Oct-17
5	Nov-17	Nov-17

Expected Outcome:

	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17
NEW	2	1	0	1	1
OPEN	2	1	1	2	2
NFA		1	0		1

Power BI Result:

	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17
NEW	2	1		1	1
OPEN	1	1	0	2	2
NFA		1			1

Hope this answered your clarifications. Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Here's the result i got.  You may download my PBI file from here.  Hope this helps.