Problem with compare date column between two row

[RubenMerino]

Hi

I make column that evalute if row has a date less than other row according this conditional:

• If value in "Data Reporting" of row with "FIV" in column "Tipus de planificació" and 1 in column "Count_FIV_per_NCH" is less than vale in "Data Reporing" of row with "NFUR" in column "Tipus de planificació" and 1 in column "Count_NFUR_per_NCH".

I don't know why return a 0 when clarly the date of FIV is less the date of NFUR. 

My code

VAR CurrentNHC = CCEE[NHC] // --> valor de la columna "NHC"

VAR FIV_Date = // --> valor data de la "FIV"
    CALCULATE( // --> Fes un càlcul
        VALUES(CCEE[Data Reporting]), // --> Agafa els valors de la columna "Data reporting"
        CCEE[Tipus de planificació] = "FIV", // --> Filtrar pel valor "FIV" a la columna "Tipus de planificació"
        CCEE[Count_FIV_per_NHC]=1, // --> Filtrat pel valor 1 a la columna "Count_FIV_per_NHC"
        CCEE[NHC] = CurrentNHC // --> El valor NHC és igual al valor de la variable "CurrentNHC"
    )
VAR NFUR_Date = // --> valor data de la "FIV"
    CALCULATE( // --> Fes un càlcul
        VALUES(CCEE[Data Reporting]), // --> Agafa els valors de la columna "Data reporting"
        CCEE[Tipus de planificació] = "NFUR", // --> Filtrar pel valor "NFUR" a la columna "Tipus de planificació"
        CCEE[Count_NFUR_per_NHC]=1, // --> Filtrat pel valor 1 a la columna "Count_NFUR_per_NHC"
        CCEE[NHC] = CurrentNHC // --> El valor NHC és igual al valor de la variable "CurrentNHC"
    )
RETURN IF(CCEE[Tipus de planificació] = "FIV" && CCEE[Count_FIV_per_NHC] = 1, IF(FIV_Date <= NFUR_Date, 1, 0))

[Anonymous · Accepted solution]

Hi @RubenMerino ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column.

Column = 
var _a=CALCULATE(MAX('Table'[Data Reporting]),FILTER('Table','Table'[NHC]=EARLIER('Table'[NHC]) && 'Table'[Count_FIV_per_NHC]=1 && 'Table'[Tipus de planificació]="FIV"))
var _b=CALCULATE(MAX('Table'[Data Reporting]),FILTER('Table','Table'[NHC]=EARLIER('Table'[NHC]) && 'Table'[Count_NFUR_per_NHC]=1 && 'Table'[Tipus de planificació]="NFUR"))
return IF('Table'[Count_FIV_per_NHC]=1 && 'Table'[Tipus de planificació]="FIV",IF(_a<=_b,1,0))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[nirali_arora]

You can use the following measure

Maxx(filter(Table, [NHC] = earlier([NHC]) && [Tipus de planificació] = "FIV"),[Date Reporting] ) - [Date Reporting]

[RubenMerino]

Hi Nirali

First, thank for your help.

But infortunally, looks like dosn't work