Lookup based on matching history id and next date based on id

[Pandadev]

I have 2 tables that are matched by history_id

within both tables there is also id which is linked by both tables.

What I am trying to do is bring in the next history row where id is matched based on the date

of the event from the match of historyid.

Example

Table 1 has 

ID     HistoryID  Date

History Table

ID     HistoryID  Date

[v-yangliu-msft]

Hi  @Pandadev  ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select = SELECTEDVALUE('Table'[DateTime])
var _1=CALCULATE(COUNT('Table'[Location]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Location]="Manchester"&&'Table'[DateTime]>=_select-28&&'Table'[DateTime]<=_select))
var _2=CALCULATE(COUNT('Table'[Location]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Location]="London"&&'Table'[DateTime]>=_select-28&&'Table'[DateTime]<=_select))
return
SWITCH(
    TRUE(),
    _1>_2,"Manchester",
    _1<_2,"London",
    BLANK())

2. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Pandadev , Try like ?

Rolling 30 day = CALCULATE(MAx(Table[Location]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))

with help from dat table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Pandadev]

I get an error saying a single value for Location in Table cannot be determined , this can happen when a column contains many values without specifying an aggregation such as min , max , count to get single result