Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Can someone help me out?
Solved! Go to 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.
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.
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |