How to calculate sum of ID with earliest date per month

[Pbiuserr]

Hello,

Considering I've got dataset like in picture below. I would like to count :

1)sum of ID consindering status B and earliest CrashDate - you can see that the earliest for ID 10001 is 31.10.2021 and I want it to be counted once for this ID (dont add others)

2) Display them on monthly basis -  10.2021 consisting sum of ID with status B and Crash Date being at 10.2021, however I dont have active relationship between CrashDate and Calendar table - its reserved for ActiveDate already

Tried myself but failed

Thank you in advance for help

[Dhacd]

Hi @Pbiuserr 

First, create a calculated column as below.

Merged = ID&CrashDate

Then use the below measure,

Unique Count Date Wise = Calculate(DistinctCount(Table[Merged]),Status="B")

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
 
Regards,
Atma.
 

[Pbiuserr]

Hi. 
Does PowerBI know which date is earliest in this approach? Because I need to count the earliest one

[Dhacd]

@Pbiuserr 
To check whether we are on the same page
We have 10001 with the crash date on 31st Oct and 30th Nov. Both with Status B
Here we should only count 31st Oct?
If this is the context use the below code as a measure.

measure = Calculate(Count(id),filter('Table',min(crashdate)=Crashdate && Status = "B"))

Regards,

Atma.

[Pbiuserr]

Yes, we should only calculate Oct30, once, for the ID of 1001. The rest with the same date means that there is change on a field which doesnt matter on the long run for that KPI

It crashes only once, so one ID can have crash = 1 in a one month, and dont repeat again unless they repeair it (but then gets status "R"). Is your formula doing that?

[Dhacd]

In that case, there will be a slight change.
Hopefully, the crash date column also has time in it.
Then the below measure will do what you require. 

measure = Calculate(distinctCount(id),filter('Table',min(crashdate)=Crashdate && Status = "B"))

[Pbiuserr]

I understand the formula and it suppose to work but I have (blank) result
I've connected this Crashdate with inactive relationship to Calendar[Date] and use USERELATIONSHIP between crashdate and calendar table but got no results

[Dhacd]

Can you confirm whether the data type of the date column is the date?

[Pbiuserr]

Yes it is. I see that it should be MINX rather than MIN? Ive created just a table from 

filter('Table',min(crashdate)=Crashdate && Status = "B"))

and I see only 3 records with earliest dates out of dataset, not per ID

[Dhacd]

Add ID column date and then this function in to the table and check

[Dhacd]

Or please share the pbix file using gdrive or onedrive so that I can take a look of the data and share the file with you.

[Pbiuserr]

Sorry cant, data is strictly prohibited to share. Can you answer my private message? I can assure that data field has data type