Forum Discussion
Runing total display
Hi fazou
Thank you very much freginier and DataNinja777 for your prompt reply.
For your question, here is the method I provided:
Here's some dummy data
“TBF_SALES”
“TBD_DATE_REFERENCE”
Create a measure.
RUNNING NET SALES SEASON = VAR CurrentWeek = MAX(TBD_DATE_REFERENCE[WEEKOFSEASON]) VAR CurrentSeason = SELECTEDVALUE(TBF_SALES[PRODUCT_SEASON]) VAR FirstSeasonWeek = MINX( FILTER( ALL(TBF_SALES), TBF_SALES[PRODUCT_SEASON] = CurrentSeason ), RELATED(TBD_DATE_REFERENCE[WEEKOFSEASON]) ) RETURN CALCULATE( SUM(TBF_SALES[NET_SALES]), FILTER( ALL(TBD_DATE_REFERENCE), TBD_DATE_REFERENCE[WEEKOFSEASON] >= FirstSeasonWeek && TBD_DATE_REFERENCE[WEEKOFSEASON] <= CurrentWeek ), TBF_SALES[PRODUCT_SEASON] = CurrentSeason, REMOVEFILTERS(TBD_DATE_REFERENCE[WEEKOFSEASON]) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello there!
Try using this formula:
RUNNING NET SALES SEASON =
VAR CurrentSeason = SELECTEDVALUE(TBF_SALES[PRODUCT_SEASON])
VAR SelectedWeek = MAX(TBD_DATE_REFERENCE[WEEKOFSEASON])
-- Find the first available week of the season
VAR FirstSeasonWeek =
CALCULATE(
MIN(TBD_DATE_REFERENCE[WEEKOFSEASON]),
ALL(TBD_DATE_REFERENCE),
ALL(TBF_SALES),
TBF_SALES[PRODUCT_SEASON] = CurrentSeason
)
RETURN
CALCULATE(
SUM(TBF_SALES[NET_SALES]),
FILTER(
ALL(TBD_DATE_REFERENCE),
TBD_DATE_REFERENCE[WEEKOFSEASON] >= FirstSeasonWeek &&
TBD_DATE_REFERENCE[WEEKOFSEASON] <= SelectedWeek
),
FILTER(
ALL(TBF_SALES),
TBF_SALES[PRODUCT_SEASON] = CurrentSeason
)
)
I believe this will help because the formula ensures that the cumulative sum starts from the first recorded week (FirstSeasonWeek) and continues up to the selected week. It also ignores direct filtering by using ALL(TBD_DATE_REFERENCE), it prevents the filter from reducing the dataset to only the selected week, allowing it to still sum up previous weeks.
If the problem is still there with this new formula, maybe double check that your WEEKOFSEASON values are numeric!
Hope this helps!
Cheerio😁😁
