Forum Discussion

Matt_Mohawk's avatar
Matt_Mohawk
Icon for Helper II rankHelper II
3 years ago

Distinct Job Count based on Start Date

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 

Need Help

4 Replies

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      Matt_Mohawk
      Icon for Helper II rankHelper II
      3 years ago

      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?

       

       

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      Matt_Mohawk
      Icon for Helper II rankHelper II
      3 years ago

      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.