Forum Discussion
Runing total display
Hello Guys,
as you seen here when i select a filter PRODUCT_SEASON it display runing total cumulative in good way.
but when i add a WEEKOFSEASON filter for exp 24W38 it display only one point with cumul value but not dispaly the cumul from 24W36 passed by 24W37 until the selected week 24W38 as shown below
this is my formula
any one have an idea how to display cumul on line chart from the start until the selected week please ?
thank you very much š
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.
9 Replies
Hi fazou ,
The issue with your RUNNING NET SALES SEASON measure is that when you apply the WEEKOFSEASON filter, it limits the dataset to only the selected week (e.g., 24W38) instead of maintaining the cumulative logic from the start of the season. To ensure that the cumulative total always starts from the first available week of the season and continues until the selected week, you need to modify your measure so that it ignores the slicerās direct filter effect on WEEKOFSEASON.
Here is the revised DAX formula:
RUNNING NET SALES SEASON = VAR CurrentWeek = MAX(TBD_DATE_REFERENCE[WEEKOFSEASON]) VAR CurrentSeason = SELECTEDVALUE(TBF_SALES[PRODUCT_SEASON]) VAR FirstSeasonWeek = MINX( FILTER( ALL(TBD_DATE_REFERENCE), TBD_DATE_REFERENCE[PRODUCT_SEASON] = CurrentSeason ), 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 ), FILTER( ALL(TBF_SALES), TBF_SALES[PRODUCT_SEASON] = CurrentSeason ) )This version ensures that the calculation always starts from the first week of the season and sums up values up to the selected week. The issue occurred because when filtering by a specific WEEKOFSEASON, Power BI restricted the dataset to only that week. By using ALL(TBD_DATE_REFERENCE), the measure ensures that all weeks remain available in the calculation. Now, even if you select 24W38, the cumulative total will still begin at the first week of the season and display all previous weeks (24W36, 24W37, etc.), maintaining a continuous accumulation in the line chart. Let me know if this resolves your issue!
Best regards,
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šš
DataNinja777 thank you for your response š
but PRODUCT_SEASON is from TBF_SALES and not TBD_DAT_REFERENCE so i didn't understand well your answer !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.
Anonymous thank you for your time š
but the end result don't match what i need,in the picture below what i need to do exactly,at the moment i'm blocked šwhat i want is when i select 24W38,the line chart display from the first week of season(here 24W36) until the selected week(here 24W38) like shown in the picture, i want to display all before the selected week !
thank you for the helpAnonymous any help please ?
