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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RubenMerino
Frequent Visitor

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. 

RubenMerino_0-1695031738529.png


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



1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @RubenMerino ,

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

(1) This is my test data. 

vtangjiemsft_0-1695195032403.png

(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.

vtangjiemsft_1-1695195065951.png

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. 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @RubenMerino ,

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

(1) This is my test data. 

vtangjiemsft_0-1695195032403.png

(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.

vtangjiemsft_1-1695195065951.png

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
Resolver II
Resolver II

You can use the following measure

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

Hi Nirali

 

First, thank for your help.

 

But infortunally, looks like dosn't work 

RubenMerino_0-1695040305759.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.