Forum Discussion

shivanirawat's avatar
shivanirawat
Frequent Visitor
3 years ago
Solved

How to get the Date Difference from a date column, if Part number of 2 row is equal to previous row.

I need a Dax expression, where I need to find out the date difference, if part number of 2nd row = Previous(Part number of 1st row).     Suppose this is the table,  where I need Dax formula,...
Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    shivanirawat 

    Days Diff = 
VAR CurrentPartNumber = 'Raw Data'[Part_Number]
VAR CurrentPartDate = 'Raw Data'[Date]
VAR SamePartRows = 
    FILTER ( 
        'Raw Data', 
        'Raw Data'[Part_Number] = CurrentPartNumber 
            && 'Raw Data'[Date] > CurrentPartDate 
    )
VAR ImmediateNextDate = 
    MINX ( SamePartRows, 'Raw Data'[Date] )
VAR Difference = 
    INT ( ImmediateNextDate - CurrentPartDate )
VAR Result = 
    IF ( COUNTROWS ( SamePartRows ) > 0, Difference, 0 )
RETURN Result

     

shivanirawat's avatar
shivanirawat
Frequent Visitor
3 years ago

Thank AntrikshSharma
This expression really worked well, but since its a huge data and I am getting some values diffference when comparing to excel data. I have also used multiple if conditions with this expression ,

VAR Result =
    IF ( COUNTROWS ( SamePartRows ) >=1, Difference, IF(COUNTROWS ( SamePartRows ) = 0, 0, BLANK()))

but not getting the exact values. I am not getting why this is happening, If you can advise something, It would be great.