Forum Discussion

RubenMerino's avatar
RubenMerino
Frequent Visitor
2 years ago
Solved

Problem with compare date column between two row

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))



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    nirali_arora
    Resolver II
    2 years ago

    You can use the following measure

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

    • RubenMerino's avatar
      RubenMerino
      Frequent Visitor
      2 years ago

      Hi Nirali

       

      First, thank for your help.

       

      But infortunally, looks like dosn't work