Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
---|---|
22 | |
14 | |
11 | |
7 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |