Forum Discussion

mtrevisiol's avatar
mtrevisiol
Helper V
4 years ago
Solved

Days between two dates in different rows

Hi everyone, I've got a table that contains the price list of some products that my company buys from a certain supplier: For each item there are the start date and the end date of the price ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • HotChilli's avatar
    HotChilli
    4 years ago

    A calculated column in DAX:

    ColumnZ = var _item = 'Table'[Item]
var _endDate = 'Table'[ValidityEndingDate]
RETURN
CALCULATE(MIN('Table'[ValidityStartingDate]), FILTER('Table', 'Table'[Item] = _item && 'Table'[ValidityStartingDate] > _endDate))

    You can then use DATEDIFF in another column to get the days between the column and the ending date.

    I haven't really tested it so please do that.

HotChilli's avatar
HotChilli
Community Champion
4 years ago

A calculated column in DAX:

ColumnZ = var _item = 'Table'[Item]
var _endDate = 'Table'[ValidityEndingDate]
RETURN
CALCULATE(MIN('Table'[ValidityStartingDate]), FILTER('Table', 'Table'[Item] = _item && 'Table'[ValidityStartingDate] > _endDate))

You can then use DATEDIFF in another column to get the days between the column and the ending date.

I haven't really tested it so please do that.

  • mtrevisiol's avatar
    mtrevisiol
    Helper V
    4 years ago

    That's great HotChilli ! I've created this column:

     

    Days between pricelists =
    var _item = 'Table'[Item]
    var _endDate = 'Table'[ValidityEndingDate]
    var _next_date = CALCULATE(MIN('Table'[ValidityStartingDate]), FILTER('Table', 'Table'[Item] = _item && 'Table'[ValidityStartingDate] >= _endDate))
    RETURN DATEDIFF('Table'[ValidityEndingDate], _next_date, DAY)