DATEDIFF ERROR

[Anonymous]

Hi Community,

Anyone knows why I am getting the below error? 

I have tried multiple versions of this measure, however none were successful. 

I want to get the number of months between those two dates, for eg:

Date Debut Effective - 01/01/2019

Date Fin Effective - 31/05/2019

M_S06 Expected Result = 5

[MFelix]

Hi @Anonymous ,

The issue here is the order of your values within the CALCULATE.

CALCULATE formula is composed by [Expression, filter1, filter 2,...] so your first value in the CALCULATE must be the difference of dates and not the ALLSELECTED since this last one is for filtering purposes and not calculation.

Redo your calculation to:

M_S06 =
CALCULATE (
    DATEDIFF (
        t_Mission[Date Début Effective];
        t_Mission[Date Fin Effective];
        MONTH
    );
    ALLSELECTED ( t_missions )
)

Regards,

MFelix

[Anonymous]

Apparentely I cannot perform the calculation.

Can you please explain me why the columns aren't available and only measures that I have in the model show up when constructing this formula? 

Thank you!

Kind Regards

[MFelix]

Hi @Anonymous ,

you are creating a measure or a column?

Regards,

MFelix

[Anonymous]

This is the error when creating a column: 

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

This occurs with measure: 

A single value for column 'Date Début Effective' in table 't_Missions' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

@MFelix 

[MFelix]

Hi @Anonymous,

In the measure this is because you are not using a aggregator while calculating the datediff, on the column doesn't make sense to have this type of error since the allselected should accept a table or a column.

Can you share a mockup file with data?

Regards,
MFelix

[Anonymous]

Hi @MFelix 

None of them works, I have tried both and the same issue occurs. 

Can you point what could potentially be happening ? I have my model well structured. I am not getting this one. 

Thanks for your help.