Distinct Job Count based on Start Date

[Matt_Mohawk]

Hi,

I need to get a distinct count of jobs based on Entered Date. This is the day that it was entered into our systems. For some reason, the data that shows is only working based on Start Date for each revision to the Job. January should have a distinct count of 22 but I am getting way more than that. I can send a pbix file if needed.

@amitchandak 

[Mikelytics]

Hi @Matt_Mohawk 

Then I am pretty sure its because of your data model. Do you have an relation between the ENtry column and the date column of your data table? If not then this is the issue. You have to create the connection. As a result it should look like this

the highlighted line is a inactive relationship.

Now you have to add in the emasure the USERELATIONSHIP function

Number of jobs =
VAR __MinDate = MIN(DimDate[Date])
RETURN
CALCULATE(
   DISTINCTCOUNT(FactTable[JobNumberColumn]),
   DimDate[Date] >= __MinDate,
   USERELATIONSHIP(DimDate[Date],FactTable[EntryColumn])
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Matt_Mohawk]

I currently have a relationship built for every table that I currently use. I use the Date Out field for that table because I have other data that I have to display other than what I had provided you. See image below to see the Relationship area.

[Mikelytics]

HI @Matt_Mohawk 

Please try the following

Number of jobs =
VAR __MinDate = MIN(DimDate[Date])
RETURN
CALCULATE(
   DISTINCTCOUNT(FactTable[JobNumberColumn]),
   DimDate[Date] >= __MinDate
)
   

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Matt_Mohawk]

@Mikelytics ,

Thank you for responding so quickly. I am still getting the same number. I have pasted an image below of the formula I created. Is it set up correctly?