Runing total display

[fazou]

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 

RUNNING NET SALES SEASON =

VAR CurrentWeek = MAX(TBD_DATE_REFERENCE[WEEKOFSEASON])

VAR CurrentSeason = SELECTEDVALUE(TBF_SALES[PRODUCT_SEASON])

-- Find the first week of the selected PRODUCT_SEASON from sales data

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

    ),

    FILTER(

        ALL(TBF_SALES),

        TBF_SALES[PRODUCT_SEASON] = CurrentSeason

    )

)

any one have an idea how to display cumul on line chart from the start until the selected week please ?

thank you very much 🙂

[fazou]

@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 ! 

[freginier]

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😁😁

[fazou]

@freginier  thank you for your help,but it still the same thing 😕 

[freginier]

Hi, sorry my solution didn't work, maybe try @DataNinja777 solution! 

[fazou]

thank you for your time anyway 🙂

[DataNinja777]

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,