Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone - I need a measure that provide a running total (RT) based on the result of another measure (Shipped). Below my data set and the current output (measures below)
Date | Paid Qty | Shipped | RT |
11/1/2021 | 2 | 0 | 0 |
12/1/2021 | 2 | 0 | 4 |
1/1/2022 | 2 | 0 | 0 |
2/1/2022 | 2 | 0 | 0 |
3/1/2022 | 12 | 12 | 16 |
4/1/2022 | 12 | 0 | 28 |
5/1/2022 | 14 | 14 | 42 |
6/1/2022 | 14 | 0 | 56 |
7/1/2022 | 14 | 0 | 70 |
8/1/2022 | 17 | 17 | 87 |
9/1/2022 | 17 | 0 | 104 |
10/1/2022 | 17 | 0 | 121 |
11/1/2022 | 17 | 0 | 138 |
12/1/2022 | 17 | 0 | 155 |
Where
[Paid Qty]
CALCULATE (
SUM ([Qty] ),
Data[Transaction] = "Paid"
)
Where [Paid PM]
CALCULATE (
SUM ( [Qty]),
DATEADD ( Data[Fiscal Date], -1, MONTH )
)
[Shipped]
IF (
[FM ID] = 0, --First month is excluded, this Measure is MAX(FiscalDateID)
0,
IF ( AND ( [Paid Qty] > [Paid Qty PM], [Paid Lics] > 0 ), [Paid Qty], 0 )
)
[RT]
CALCULATE (
[Shipped],
DATESYTD('Data'[Fiscal Date])
)
The output I'm looking for is
Date | Paid Qty | Shipped | RT |
11/1/2021 | 2 | 0 | 0 |
12/1/2021 | 2 | 0 | 0 |
1/1/2022 | 2 | 0 | 0 |
2/1/2022 | 2 | 0 | 0 |
3/1/2022 | 12 | 12 | 12 |
4/1/2022 | 12 | 0 | 12 |
5/1/2022 | 14 | 14 | 14 |
6/1/2022 | 14 | 0 | 14 |
7/1/2022 | 14 | 0 | 14 |
8/1/2022 | 17 | 17 | 17 |
9/1/2022 | 17 | 0 | 17 |
10/1/2022 | 17 | 0 | 17 |
11/1/2022 | 17 | 0 | 17 |
12/1/2022 | 17 | 0 | 17 |
I can't figure this one out, so I'd really appreciate some help!
Hi @artlearning,
I'm not so clear how the qty expression calculated. Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |