Forum Discussion

fazou's avatar
fazou
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

     

     

     

     

DataNinja777's avatar
DataNinja777
Super User
1 year ago

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,