Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Days Difference between two dates in same column with duplicate dates in Power BI

I have a column with duplicate dates. I need to calculate date difference based on another column. For example, I have Stage column with 1,2,3... repeated values . What will be function I can use to...
Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    maybe you can try this

    measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))

  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks a lot. It worked for me...😊

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

 

 

Days Diff Measure : =
VAR ranking =
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
ASC
)
VAR result =
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Stage] = MAX ( 'Table'[Stage] )
&& RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
ASC
) = ranking - 1
)
),
MAX ( 'Table'[Date] ),
DAY
)
RETURN
result

 

 

https://www.dropbox.com/s/rwbkhoz3ir6d789/erp.pbix?dl=0