Calculate average number of working days between two dates

[Anonymous]

Hello,

I'm using a table F_DOSSIERS with the fields: PK_DOSSIER, RECIEVED_DATE, D_CREATION_DATE, FIRST_DOCUMENTS_DATE and ADMINISTRATOR

I have a date table D_DATES with the fields: 

BK_DATE, WEEKDAY_ABR, WEEK_NUMBER, FIRST_OF_WEEK, LAST_OF_WEEK, MONTH_ABR, MONTH_NUMBER, FIRST_OF_MONTH, LAST_OF_MONTH, QUARTER_NUMBER, FIRST_OF_QUARTER, LAST_OF_QUARTER, YEAR_NUMBER, FIRST_OF_YEAR, LAST_OF_YEAR and IS_WORKING_DAY

BK_DATE contains the date

IS_WORKING_DAY contains the value 1 if the date is a working day and contains a 0 when the date is no working day: that's not only the weekends (Saterday and Sunday) but also special holidays like New Year, First of May, Ascension Day, Pentecost Monday, ...

I renamed D_DATES as D_CREATION_DATES and linked it with F_DOSSIERS:

D_CREATION_DATES[BK_DATE] -> F_DOSSIERS[CREATION_DATE]

A copy of D_DATES was renamed as D_FIRST_DOCUMENTS_DATES and also linked with D_DOSSIERS:

D_FIRST_DOCUMENTS_DATES[BK_DATE] -> F_DOSSIERS[FIRST_DOCUMENTS_DATE]

A copy of D_DATES was renamed as D_RECEIVED_DATES and also linked with D_DOSSIERS:

D_RECEIVED_DATES[BK_DATE] -> F_DOSSIERS[RECEIVED_DATE]

I need to calculate for every dossier record in F_DOSSIERS the average number of working days between F_DOSSIERS[RECEIVED_DATE] and  F_DOSSIERS[FIRST_DOCUMENTS_DATE]. 
However, in case  F_DOSSIERS[RECEIVED_DATE] has no value (Null) then the average numer of working days between F_DOSSIERS[CREATION_DATE] and  F_DOSSIERS[FIRST_DOCUMENTS_DATE] is calculated instead.

How can this be done? 

Thanks,

R.W.

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

I think you can get what you are looking for with just one date table and a couple measure.

First we have a measure to calculate the working days.  This uses SELECTEDVALUE to pick up CREATION_DATE if RECEIVED_DATE is null.

Working Days = 
VAR _StartDate = SELECTEDVALUE(F_DOSSIERS[RECEIVED_DATE],SELECTEDVALUE(F_DOSSIERS[D_CREATION_DATE]))
VAR _EndDate = SELECTEDVALUE(F_DOSSIERS[FIRST_DOCUMENTS_DATE])

RETURN
    CALCULATE(
        SUM(DATES[IS_WORKING_DAY]),
        DATES[BK_DATE] >= _StartDate,
        DATES[BK_DATE] <= _EndDate
    )

Then an AVERAGEX of the [Working Days] measure over the F_DOSSIERS[PK_DOSSIER] list to get the average.

Avg Working Days = 
AVERAGEX(
    VALUES(F_DOSSIERS[PK_DOSSIER]),[Working Days]
)

 

[Greg_Deckler]

@Anonymous Kind of a lot going on in that post. Sample data that can be copied would be fantastic along with expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.