Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

Hi @Anonymous ,

 

you are creating a measure or a column?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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 

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.