Measure for work days per month based off job

[NolanB]

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.

[tamerj1 · Accepted solution]

@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

[tamerj1]

Hi @NolanB 

Try

SUMX (

Job,

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

)

[NolanB]

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,

[tamerj1]

@NolanB 

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

[NolanB]

@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

[NolanB]

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