cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DATEDIFF ERROR

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

 

123.JPG

6 REPLIES 6
Super User
Super User

Re: DATEDIFF ERROR

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

Re: DATEDIFF ERROR + COLUMNS DONT APPEAR IN CALCULATE ONLY MEASURES

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

 

Capture.JPG

Super User
Super User

Re: DATEDIFF ERROR + COLUMNS DONT APPEAR IN CALCULATE ONLY MEASURES

Hi @Anonymous ,

 

you are creating a measure or a column?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

Re: DATEDIFF ERROR + COLUMNS DONT APPEAR IN CALCULATE ONLY MEASURES

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. 

Anonymous
Not applicable

Re: DATEDIFF ERROR + COLUMNS DONT APPEAR IN CALCULATE ONLY MEASURES

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 

Super User
Super User

Re: DATEDIFF ERROR + COLUMNS DONT APPEAR IN CALCULATE ONLY MEASURES

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 134 members 1,696 guests
Please welcome our newest community members: