topic Re: Measure for work days per month based off job in DAX Commands and Tips

Measure for work days per month based off job

NolanB — Tue, 05 Apr 2022 16:22:04 GMT

Hello,

I have two tables; Jobs & Date Table

Jobs contains columns; Job, Start Date, End Date

Date Table contains columns; Date,Year&Month(text string with year and month of date),WorkDay?(True or false)

These tables are related by Jobs(Start Date) to Date Table(Date) in the type many to one

I want a matrix that shows Jobs(job) as columns and Date table(Year&month) as rows. I want my values to be the number of work days in each month for each job but every measure i have attempted for this has failed. Any help or advice would be appreciated!

Thanks, Nolan.

Re: Measure for work days per month based off job

tamerj1 — Tue, 05 Apr 2022 16:29:01 GMT

Hi NolanB

Try

SUMX (

Job,

DATEDIFF ( Job[Start Date], Job[End Date], DAY )

)

Re: Measure for work days per month based off job

NolanB — Tue, 05 Apr 2022 16:47:16 GMT

Hi tamerj1

I appreciate your response,

But this only gives me the duration of each job, what i am looking for is the work days in each month for each job

Thanks,

Re: Measure for work days per month based off job

tamerj1 — Tue, 05 Apr 2022 17:11:09 GMT

NolanB

Can you please share a screen shot id your report and the results you got?

Re: Measure for work days per month based off job

NolanB — Tue, 05 Apr 2022 17:42:34 GMT

tamerj1 I have used dummy data, as the original contains some confidential information, but the results are the same

I have included the two tables as well as the result

Re: Measure for work days per month based off job

NolanB — Tue, 05 Apr 2022 17:48:33 GMT

I have quickly constructed this to more clearly explain the matrix i wish to generate as a result. the numbers are not accurate just wanted to clarify

Re: Measure for work days per month based off job

tamerj1 — Tue, 05 Apr 2022 18:56:13 GMT

NolanB
Here is the sample file with the solution https://www.dropbox.com/t/EjgpPwGXSmF36f4z

No need for relationship between the two tables.

The measure code is

Number of Day = VAR LastDateInFilter = MAX ( 'Date'[Date] ) VAR FirstDateInFilter = MIN ( 'Date'[Date] ) VAR MinVisibleStartDate = MIN ( Job[Start Date] ) VAR MaxVisibleEndDate = MAX ( Job[End Date] ) VAR AvialableWrkingDays = CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[IsWorkDay] = TRUE() ) VAR SatrtToEnd = CALENDAR ( MinVisibleStartDate, MaxVisibleEndDate ) VAR Result = COUNTROWS ( INTERSECT ( AvialableWrkingDays, SatrtToEnd ) ) RETURN Result