Limit running total by date

[Anonymous]

I have two datasets

Plans

PlanId | StartDate | Article | Required

1         | 01.01.22 |     a      |      5

1         | 02.01.22 |     a      |      10

1         | 02.01.22 |     b     |      5

Deliveries

DeliveryId | DeliveryDate | Article | RunningTotal

1               |   01.01.22      |      a     |    2

1               |   02.01.22      |      a     |    4

1               |   03.01.22      |      a     |    6

1               |   03.01.22      |      b     |    6

I would now like to create a Measure with this result

PlanId | StartDate | Article | Required  | DeliveredByStartDate

1         | 01.01.22 |     a      |      5          |         2

1         | 02.01.22 |     a      |      10        |         4

1         | 03.01.22 |     b     |      5           |         6

The DeliveredByStartDate should select all deliveries matching the plan by Article, where the DeliveryDate is <= StartDate and then get the Max of the RunningTotals.

I have connected the data sets with an m:n relationship on "Article" so this covers the filtering by article

Then I have been able to get the Max of the RunningTotal of the Deliveries:

DeliveredByStartDate = CALCULATE(MAX(Deliveries[RunningTotal]), /*WHAT FILTER GOES HERE?*/)

But I am missing a Filter to filter the Deliveries by those <= Plan StartDate

Can someone help me out?

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please try following measure:

DeliveredByStartDate = CALCULATE(MAX('Deliveries Table'[Running Toal]),FILTER('Deliveries Table','Deliveries Table'[DeliveryDate] <= MAX('Plan Table'[StartDate])))

Thwe result you want:

Best regards,

Yadong Fang

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

[lbendlin]

I have connected the data sets with an m:n relationship on "Article" so this covers the filtering by article

Your data model seems to be missing a calendar table.