Limit running total by date

Question

I have two datasets&nbsp;PlansPlanId | StartDate | Article | Required1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 01.01.22 |&nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 51&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 02.01.22 |&nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 02.01.22 |&nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 5&nbsp;DeliveriesDeliveryId | DeliveryDate | Article | RunningTotal1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp;01.01.22&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp;02.01.22&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; 41&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp;03.01.22&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; a&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; 61&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp;03.01.22&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; 6&nbsp;&nbsp;I would now like to create a Measure with this result&nbsp;PlanId | StartDate | Article | Required&nbsp; | DeliveredByStartDate1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 01.01.22 |&nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 02.01.22 |&nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;41&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 03.01.22 |&nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp;The DeliveredByStartDate should select all deliveries matching the plan by Article, where the DeliveryDate is &lt;= StartDate and then get the Max of the RunningTotals.&nbsp;I have connected the data sets with an m:n relationship on "Article" so this covers the filtering by article&nbsp;Then I have been able to get the Max of the RunningTotal of the Deliveries:DeliveredByStartDate = CALCULATE(MAX(Deliveries[RunningTotal]), /*WHAT FILTER GOES HERE?*/)&nbsp;But I am missing a Filter to filter the Deliveries by those &lt;= Plan StartDate&nbsp;&nbsp;Can someone help me out?

v-yadongf-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Please try following measure:
DeliveredByStartDate = CALCULATE(MAX('Deliveries Table'[Running Toal]),FILTER('Deliveries Table','Deliveries Table'[DeliveryDate] &lt;= MAX('Plan Table'[StartDate])))
&nbsp;
Thwe result you want:

&nbsp;
Best regards,
Yadong Fang
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Limit running total by date

2 Replies

Related content

Running Totals

Running total

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue

SumX? - cumulative running total