topic Limit running total by date in DAX Commands and Tips

Limit running total by date

Anonymous — Fri, 21 Oct 2022 11:13:04 GMT

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?

Re: Limit running total by date

lbendlin — Sun, 23 Oct 2022 00:39:37 GMT

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.

Re: Limit running total by date

v-yadongf-msft — Wed, 26 Oct 2022 09:25:21 GMT

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.