Identify Unique ids based on two month periods

[Anonymous]

I am trying to re-create  "New case" in power bi that would identify as Yes if its is the first new entry of a uniqe case ID. 

the Column would be as below

Uniqe Case ID	Date Logged	Completed Date	Report Month	New Cases
ID1	2020-01-21	-	Jan	Yes
ID1	2020-01-21	-	Feb	No
ID1	2020-01-21	2020-03-22	March	No
ID2	2020-02-01	-	Feb	Yes
ID2	2020-02-01	2020-03-02	March	No
ID3	2020-03-01	2020-03-12	March	Yes
ID4	2020-01-01	2020-01-09	Jan	Yes

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, you need add a Report Month No column in the table.

for example, you could use this formula to get it.

Report Month No = SWITCH('Table'[Report Month],
"Jan",1,
"Feb",2,
"March",3
)

Then use this formula to get the expected output column:

Result New cases = 
IF( CALCULATE(MIN('Table'[Report Month No]),FILTER('Table','Table'[Uniqe Case ID]=EARLIER('Table'[Uniqe Case ID])))='Table'[Report Month No],"Yes","No")

Result:

and here is sample pbix file, please try it

Regards,

Lin

[Pragati11]

Hi @Anonymous ,

Your requirement is not complete as you haven't mentioned how the first new entry against a Unique Case ID is considered.

Is it based on Report Month or somehting using Date Logged and Completed Date?

Thanks,

Pragati

[Anonymous]

What i am trying to capture in each report month is when the unique ID was first loggged  based on date logged.

So if it was logged in  Jan, then in report month it should identify it as a new case. where as if it was logged in in March, then the report month for March should identify it as a new case.