Reply
Highlighted
Frequent Visitor
Posts: 11
Registered: ‎04-25-2018
Accepted Solution

How to Compare two date fields?

I want to compare two date fields or set a value in the "Distancia" field when is showing the oldest date field ("Fecha").

 

I have this:

'Registro Diario' is a table.

A column called "Fecha" and a measure called "Prueba"; "Fecha" comes from a excel file and "Prueba" is calculated as follows

 

Prueba=CALCULATE ( MIN ( 'Registro Diario'[Fecha] ); ALLSELECTED ( 'Registro Diario' ) )

 

The Distancia field is calculated as follows:

 

Distancia (KM)  = IF('Registro Diario'[Fecha]='Registro Diario'[Prueba];0;1)

 

soporte4.png

 

 

 

I appreciate your help


Accepted Solutions
Super User
Posts: 1,571
Registered: ‎05-10-2016

Re: How to Compare two date fields?

@afcamacho

 

Hi, instead of using a calculated column you need to use a measure

 

D2 =
IF ( MIN ( 'Registro Diario'[Fecha] ) = [Prueba]; 0; 1 )

Regards

 

Victor

Lima - Peru
Power BI

View solution in original post

Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: How to Compare two date fields?

Hi @afcamacho,

 

Looking at your PBIX file this as to be change to a measure as I refered in my first post and as @Vvelarde also indicates in his response, if you do that it will give you 0 for the first record and 1 for all the others

 

pozo.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post


All Replies
Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: How to Compare two date fields?

Hi @afcamacho,

 

Is Distancia KM a measure or a column?

 

Try to change it to this measure:

 

Distancia (KM)  = IF(MAX('Registro Diario'[Fecha])=[Prueba];0;1)

Regards,

MFelix



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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎04-25-2018

Re: How to Compare two date fields?

Hi @MFelix, thanks for answering

 

It doesn't work, I shows the following

 

Soporte5.png

 

 

 

"Distancia (KM)" is a column of type "Whole number" and format "Decimal Number", "Fecha" is a column of type "Date" and format (d/MM/yyyy), "Prueba" is a measure without type and format (d/MM/yyyy)

 

 

Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: How to Compare two date fields?

Hi @afcamacho,

 

can you share a sample of the PBIX file?

 

Regards,

MFelix



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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎04-25-2018

Re: How to Compare two date fields?

[ Edited ]
Super User
Posts: 1,571
Registered: ‎05-10-2016

Re: How to Compare two date fields?

@afcamacho

 

Hi, instead of using a calculated column you need to use a measure

 

D2 =
IF ( MIN ( 'Registro Diario'[Fecha] ) = [Prueba]; 0; 1 )

Regards

 

Victor

Lima - Peru
Power BI
Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: How to Compare two date fields?

Hi @afcamacho,

 

Looking at your PBIX file this as to be change to a measure as I refered in my first post and as @Vvelarde also indicates in his response, if you do that it will give you 0 for the first record and 1 for all the others

 

pozo.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




Frequent Visitor
Posts: 11
Registered: ‎04-25-2018

Re: How to Compare two date fields?

Hi @MFelix and @Vvelarde

 

I apologize because I said something wrong, in fact the distance field that I have as a column is because I need to perform the following calculation

 

Distancia (KM)= var Lat1='Registro Diario'[Latitud - Distancia] * 3,1415 / 180
var Lat2 = 'Registro Diario'[Latitud - Distancia.Anterior] * 3,1415 / 180
var Lon1 = 'Registro Diario'[Longitud - Distancia] * 3,1415 / 180
var Lon2 = 'Registro Diario'[Longitud - Distancia.Anterior] * 3,1415 / 180
var valor = Cos( Lat1 ) * Cos( Lat2 ) * Cos( Lon2 - Lon1 ) + Sin( Lat1 ) * Sin( Lat2 )
return IF(valor<0,99999999999999999999;IF('Registro Diario'[Fecha]=CALCULATE ( MIN ( 'Registro Diario'[Fecha] ); ALLSELECTED ( 'Registro Diario' ) );0;6378*ACOS(valor));0)

 

When I try to pass it as a measure this columns weren't recognized

 

'Registro Diario'[Longitud - Distancia.Anterior]

'Registro Diario'[Longitud - Distancia]

'Registro Diario'[Latitud - Distancia.Anterior]

'Registro Diario'[Latitud - Distancia]

 

 

I apologize for the lack of information

 

 

 

Super User
Posts: 1,477
Registered: ‎09-19-2016

Re: How to Compare two date fields?

Hi @afcamacho,

 

Try to use a MAX or MIN function on those columns to make context on them.

 

Regards,

MFelix



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

Proud to be a Datanaut!