Need help with DAX on getting Max Date of a given month

[Maheshguptaz]

Hi All,

I'm trying to find the distinct count of Job ID based on max DateKey. I'm using below DAX to get my desired results

HActiveJobsMonth =

VAR _Sele   =CONVERT(max(JobReportHistory[DateKey]),INTEGER)

VAR _jobs = CALCULATE( DISTINCTCOUNT(JobReportHistory[Job ID]), JobReportHistory[Job Status] in {"Created", "Interview", "Offer", "Sourcing"}, JobReportHistory[Job Approval Status]<>"REJECTED" && JobReportHistory[Job Approval Status]<>"PENDING")

RETURN

CALCULATE(_jobs,FILTER('Shared DW_DimDate','Shared DW_DimDate'[Date_Key]=_Sele))


However, I'm not getting the value that I'm expecting (1036). It's giving the total for that month as 1226. Please refer to the screenshot below:

Can anyone help me with the DAX to get the right value?

Thanks in advance!

Regards,
Mahesh

[Anonymous]

Hi, @Maheshguptaz 

May I ask if you have resolved this issue? As per your description, you have JobReportHistory[Job Status] column, JobReportHistory[Job Approval Status] column, 'Shared DW_DimDate'[Date_Key] column in your Measure which is related to your requirement, you can Provide a full simulation of the problem with a datasheet/pbix file without sensitive data for testing, which will help you to solve the problem faster.

Best Regards,
Yang
Community Support Team

[PhilipTreacy]

Hi @Maheshguptaz 

Please supply your file.  It's much easier to help you when we have your data to work with.

Regards

Phil

[Maheshguptaz]

Hi @PhilipTreacy ,

I won't be able to share the data as it contains sensitive information. Hope you understand my limitations.