Hello @Kuri_191 ,
You need to calculate the running day number for each product — this means the first date a product has sales is Day 1, the next date is Day 2, etc., even if some dates are missing in between. This is an calculated column DAX:
DayNumber =
RANKX(
FILTER(
Sales,
Sales[Product] = EARLIER(Sales[Product])
),
Sales[Date],
,
ASC,
DENSE
)
This gives you the Day column you want per product.
And then you need to calculate daily sum per product, create a measure:
Daily Sales = SUM(Sales[Amount])
Now create a cumulative measure:
Cumulative Sales =
CALCULATE(
[Daily Sales],
FILTER(
ALL(Sales),
Sales[Product] = MAX(Sales[Product]) &&
Sales[DayNumber] <= MAX(Sales[DayNumber])
)
)
For your last need:
---Create a Date table in your model — make sure it covers all dates.
---Create a Product-Day matrix:
-Add Date from the Date table.
-Add Product.
-Show Cumulative Sales measure.
---In your visual → turn on “Show items with no data” for the Date axis.
Final Cumulative Sales =
VAR LastValue =
CALCULATE(
[Cumulative Sales],
FILTER(
ALL(Sales),
Sales[Product] = MAX(Sales[Product]) &&
Sales[DayNumber] <= MAX(Sales[DayNumber])
)
)
RETURN
COALESCE(LastValue,
CALCULATE(
MAXX(Sales, [Cumulative Sales]),
FILTER(
ALL(Sales),
Sales[Product] = MAX(Sales[Product]) &&
Sales[DayNumber] < MAX(Sales[DayNumber])
)
)
)
If this solved your issue, please mark it as the accepted solution. ✅
