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)