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 🙂

[Anonymous · Accepted solution]

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.

[fazou]

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

[fazou]

@Anonymous  any help please ?

[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,