Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
i have a table below which i need running table , how we can achieve
first 3 columns we can get from SQL data base and how we can make it output
Product | Date | No. of units sold | Output |
A | 1/1/2023 | 90 | 90 |
A | 1/12/2023 | 100 | 190 |
A | 1/15/2023 | 150 | 340 |
A | 1/4/2023 | 123 | 463 |
B | 1/5/2023 | 300 | 300 |
B | 1/6/2023 | 400 | 700 |
B | 1/7/2023 | 500 | 1200 |
B | 1/8/2023 | 600 | 1800 |
C | 1/12/2023 | 60 | 60 |
C | 1/15/2023 | 70 | 130 |
C | 2/11/2023 | 80 | 210 |
C | 2/12/2023 | 90 | 300 |
or you implement with a measure:
Measure =
VAR CurrentProduct = MAX(TableName[Product])
VAR CurrentDate = MAX(TableName[Date])
RETURN
SUMX(
FILTER(
ALL(TableName),
TableName[Product]=CurrentProduct
&&TableName[Date]<=CurrentDate
),
TableName[No. of units sold]
)
hi @MSMKM
try to add a calculated column like:
Output2 =
SUMX(
FILTER(
TableName,
TableName[Product]=EARLIER(TableName[Product])
&&TableName[Date]<=EARLIER(TableName[Date])
),
TableName[No. of units sold]
)
or
Output3 =
VAR CurrentProduct = [Product]
VAR CurrentDate = [Date]
RETURN
SUMX(
FILTER(
TableName,
TableName[Product]=CurrentProduct
&&TableName[Date]<=CurrentDate
),
TableName[No. of units sold]
)
Hi @MSMKM ,
You can create this measure and add it to your visual:
Running total =
SUMX(
WINDOW(
1, ABS,
0, REL,
ORDERBY(
'Table'[Product], ASC,
'Table'[Date], ASC
)
),
CALCULATE( SUM( 'Table'[No. of units sold] ) )
)
I think this can do the trick.
Best,