Measures: Calculate difference between dates split per month

[Anonymous]

Hi All, 

I have a table such as: 

REFERENCE NUMBER	REQUEST DATE	VALIDATION DATE	COMPLETION DATE
G_EUR_GBR_DSF00000028	22/11/2018	07/12/2018	17/12/2019

The result that i was expeting was 8 days for novemeber and 7 days for December for validation delay.  So I created a the following mesures :

.REC_Validation_Delay= 
VAR REQDate = VALUE( SELECTEDVALUE( RECRUITMENT[Request Date]) )
VAR VALDate = IF( ISBLANK(VALUE( SELECTEDVALUE( 'RECRUITMENT'[Validation Date] ) )),Today(),VALUE( SELECTEDVALUE( RECRUITMENT[Validation Date] ) ))
VAR MinDateInContext = VALUE( MIN( 'calendar'[Date] ) )
VAR MaxDateInContext = VALUE( MAX( 'calendar'[Date] ) )
RETURN
IF( AND( REQDate < MinDateInContext, VALDate > MinDateInContext ) ,
        MIN( VALDate, MaxDateInContext ) - MinDateInContext ,
            IF( AND( AND( REQDate > MinDateInContext, REQDate < MaxDateInContext ), VALDate > MinDateInContext ),
                 MIN( VALDate, MaxDateInContext ) - REQDate , 
                   BLANK() ) )

 And a second mesure which returns the real value for the item but the average in the total 

Average_validation_Delay = 
AVERAGEX( 
    VALUES( RECRUITMENT[Reference Number] ),
        [.REC_Validation_Delay] )

When I put the average_validation_delay measure into a table/matrix, it's returns the correct figure for NOV but no blank values for DEC and the total correct 15 days.

I am also facing issue with another measure that i have used the same code just change the start date as validation and end date as completion date to calculat time to fill: 

.REC_Time_to_fill = 
VAR REQDate = VALUE( SELECTEDVALUE( RECRUITMENT[Validation Date] ) )
VAR VALDate = IF( ISBLANK(VALUE( SELECTEDVALUE( RECRUITMENT[Completion Date] ) )),Today(),VALUE( SELECTEDVALUE( RECRUITMENT[Completion Date] ) ))
VAR MinDateInContext = VALUE( MIN( 'calendar'[Date] ) )
VAR MaxDateInContext = VALUE( MAX( 'calendar'[Date] ) )
RETURN
IF( AND( REQDate < MinDateInContext, VALDate > MinDateInContext ) ,
        MIN( VALDate, MaxDateInContext ) - MinDateInContext ,
            IF( AND( AND( REQDate > MinDateInContext, REQDate < MaxDateInContext ), VALDate > MinDateInContext ),
                 MIN( VALDate, MaxDateInContext ) - REQDate , 
                   BLANK() ) )

REC - Time to Hire (Avg) = 
AVERAGEX( 
    VALUES( RECRUITMENT[Reference Number] ),
        [.REC_Time_to_fill] )

Any clue what is wrong ? 

the relationship between the recrtuiment table and calendar date has 3 dates interfaces inactive: 

[amitchandak · Accepted solution]

@Anonymous , this file has split of dates on daily range. check if this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Anonymous]

Thanks a lot 🙂

I worked properly and faster than the measure that i create initially.