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,

                                                                                Days = If(A3=A2, B3-B2, "False")

      And things to take care: Part_number column should be in Ascending Order  and Date Column should must be sorted from Newest to oldest. 

 

I have also tried some Dax expression, But not getting correct results,

 

Days_Diff =
var _PartNumber= 'Raw Data'[PartNumber]
var _date = CALCULATE(MAX('Raw Data'[RawData[Date]),FILTER('Raw Data',[RawData[Date]< EARLIER('Raw Data'[RawData[Date])),'Raw Data'[PartNumber]= _PartNumber)
var _datediff = DATEDIFF(_date, 'Raw Data'[RawData[Date],DAY)
return
        _datediff
   
  I have also tried using index, but getting this error: 
column names are different in above snap shot, mentioning Column names for reference,
[Merged]->  As [Part_Number]
RawData[Open] -> As [date]
Raw Data -> Table Name
 

_datediff

 

 

Please advise me DAX expression.

 

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

     

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    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.