Solved: Re: Taking a Cumulative total of a Measure Derived...

OldOak123 · ‎07-11-2024

Hi, I am trying to calculate a cumulative total for a measure which I have calculated from the var named 'AvgwithPhasing'. If I just return 'AvgWithPhasing' as the final output, I seem to be getting the same output as I would do with the below DAX (where it is just a sum for the individual week, rather than cumulative). Would someone be able to help me edit the following so that it returns the second column, so that the output from the var 'AvgWithPhasing' is made to be cumulative please? I'm assuming I've got something wrong with the final filter context...

Cumulative Total =

VAR SixWeekAverage =
CALCULATE(
AVERAGEX(
FILTER(
SUMMARIZE(Sales,'Calendar'[Previous 6 Weeks],'Calendar'[FY Week]),
'Calendar'[Previous 6 Weeks]=1),
[Total Sales]
),
ALL('Calendar'[FY Week])

)

VAR AvgwithPhasing =

SUMX(
SUMMARIZE(
FILTER('Calendar', 'Calendar'[MaxYear]=1),
'Calendar'[FY Week],
"6weekaveragephasing", SixWeekAverage*MAX(Phasing[Phasing]))
,
[6weekaveragephasing]
)

RETURN

CALCULATE (
AvgwithPhasing ,
FILTER (
ALL ( 'Calendar'[FY Week] ),
'Calendar'[FY Week] <= MAX ( ( 'Calendar'[FY Week] ) )
)
)

fy week	Current Output	Intended output
1	50100	50100
2	24000	74100
3	28111	102211
4	56874	159085
5	55645	214730
6	54568	269298
7	68746	338044

Anonymous · ‎07-22-2024

Hi,@OldOak123

Thanks for the quick reply, sorry for the late reply.

Regarding the issue you raised, my solution is as follows:

1.Next create relationships between tables:

2.Create calculated column references:

Column = 
VAR kk1 =
    CALCULATE (
        AVERAGE ( 'Sales'[Total_Sales] ),
        FILTER (
            SUMMARIZE (
                'Sales',
                'Calendar'[Previous 6 Weeks],
                'Calendar'[FY Week],
                'Sales'[Total_Sales]
            ),
            'Calendar'[Previous 6 Weeks] = 1
        )
    )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[MaxYear] = 1 ),
            'Calendar'[FY Week],
            "6weekaveragephasing", kk1 * MAX ( Phasing[Phasing] )
        ),
        [6weekaveragephasing]
    )

3. Below are the measure I've created for your needs:

MEASURE = 
IF (
    ISBLANK ( MAX ( 'Calendar'[Column] ) ),
    BLANK (),
    CALCULATE (
        SUM ( 'Calendar'[Column] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[FY Week] <= MAX ( 'Calendar'[FY Week] )
        )
    )
)

4.Here's my final result, which I hope meets your requirements.

To sum up, we first modify the measure value of the previous calculation required value to the calculated column, and then create the measure to realize the result of referencing the cumulative calculation column.

If you have any questions, please feel free to contact us.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

OldOak123 · ‎07-22-2024

Accepted as solution, thank you.

Anonymous · ‎07-22-2024

Hi,@OldOak123

We are very glad to know that the issue has been resolved. If you wish, consider accepting my solution as a solution that will also benefit other community members who have the same problem as you and find a solution faster.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OldOak123 · ‎07-19-2024

Hi, just wanted to see if you had a solution for my query?

Anonymous · ‎07-22-2024

Hi,@OldOak123

Thanks for the quick reply, sorry for the late reply.

Regarding the issue you raised, my solution is as follows:

1.Next create relationships between tables:

2.Create calculated column references:

Column = 
VAR kk1 =
    CALCULATE (
        AVERAGE ( 'Sales'[Total_Sales] ),
        FILTER (
            SUMMARIZE (
                'Sales',
                'Calendar'[Previous 6 Weeks],
                'Calendar'[FY Week],
                'Sales'[Total_Sales]
            ),
            'Calendar'[Previous 6 Weeks] = 1
        )
    )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[MaxYear] = 1 ),
            'Calendar'[FY Week],
            "6weekaveragephasing", kk1 * MAX ( Phasing[Phasing] )
        ),
        [6weekaveragephasing]
    )

3. Below are the measure I've created for your needs:

MEASURE = 
IF (
    ISBLANK ( MAX ( 'Calendar'[Column] ) ),
    BLANK (),
    CALCULATE (
        SUM ( 'Calendar'[Column] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[FY Week] <= MAX ( 'Calendar'[FY Week] )
        )
    )
)

4.Here's my final result, which I hope meets your requirements.

To sum up, we first modify the measure value of the previous calculation required value to the calculated column, and then create the measure to realize the result of referencing the cumulative calculation column.

If you have any questions, please feel free to contact us.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OldOak123 · ‎07-16-2024

Hi, due to company policy, I'm unable to share SharePoint/Onedrive links. Please see a dummy version of the data which is being used below.
Calendar -> Phasing is M:1 on 'FY Week' to 'Week'
Calendar -> Sales is 1:M on 'date' to 'Week Start'

Calendar

Date,FY Week,FY Year Week,FY,MaxYear,Before Max Date,Previous 6 Weeks
07/04/2024,1,202401,2024,1,1,0
06/04/2024,1,202401,2024,1,1,0
05/04/2024,1,202401,2024,1,1,0
04/04/2024,1,202401,2024,1,1,0
03/04/2024,1,202401,2024,1,1,0
02/04/2024,1,202401,2024,1,1,0
01/04/2024,1,202401,2024,1,1,0
14/04/2024,2,202402,2024,1,1,0
13/04/2024,2,202402,2024,1,1,0
12/04/2024,2,202402,2024,1,1,0
11/04/2024,2,202402,2024,1,1,0
10/04/2024,2,202402,2024,1,1,0
09/04/2024,2,202402,2024,1,1,0
08/04/2024,2,202402,2024,1,1,0
21/04/2024,3,202403,2024,1,1,0
20/04/2024,3,202403,2024,1,1,0
19/04/2024,3,202403,2024,1,1,0
18/04/2024,3,202403,2024,1,1,0
17/04/2024,3,202403,2024,1,1,0
16/04/2024,3,202403,2024,1,1,0
15/04/2024,3,202403,2024,1,1,0
28/04/2024,4,202404,2024,1,1,1
27/04/2024,4,202404,2024,1,1,1
26/04/2024,4,202404,2024,1,1,1
25/04/2024,4,202404,2024,1,1,1
24/04/2024,4,202404,2024,1,1,1
23/04/2024,4,202404,2024,1,1,1
22/04/2024,4,202404,2024,1,1,1
05/05/2024,5,202405,2024,1,1,1
04/05/2024,5,202405,2024,1,1,1
03/05/2024,5,202405,2024,1,1,1
02/05/2024,5,202405,2024,1,1,1
01/05/2024,5,202405,2024,1,1,1
30/04/2024,5,202405,2024,1,1,1
29/04/2024,5,202405,2024,1,1,1
12/05/2024,6,202406,2024,1,1,1
11/05/2024,6,202406,2024,1,1,1
10/05/2024,6,202406,2024,1,1,1
09/05/2024,6,202406,2024,1,1,1
08/05/2024,6,202406,2024,1,1,1
07/05/2024,6,202406,2024,1,1,1
06/05/2024,6,202406,2024,1,1,1
19/05/2024,7,202407,2024,1,1,1
18/05/2024,7,202407,2024,1,1,1
17/05/2024,7,202407,2024,1,1,1
16/05/2024,7,202407,2024,1,1,1
15/05/2024,7,202407,2024,1,1,1
14/05/2024,7,202407,2024,1,1,1
13/05/2024,7,202407,2024,1,1,1
26/05/2024,8,202408,2024,1,1,1
25/05/2024,8,202408,2024,1,1,1
24/05/2024,8,202408,2024,1,1,1
23/05/2024,8,202408,2024,1,1,1
22/05/2024,8,202408,2024,1,1,1
21/05/2024,8,202408,2024,1,1,1
20/05/2024,8,202408,2024,1,1,1
02/06/2024,9,202409,2024,1,1,1
01/06/2024,9,202409,2024,1,1,1
31/05/2024,9,202409,2024,1,1,1
30/05/2024,9,202409,2024,1,1,1
29/05/2024,9,202409,2024,1,1,1
28/05/2024,9,202409,2024,1,1,1
27/05/2024,9,202409,2024,1,1,1
09/06/2024,10,202410,2024,1,0,0
08/06/2024,10,202410,2024,1,0,0
07/06/2024,10,202410,2024,1,0,0
06/06/2024,10,202410,2024,1,0,0
05/06/2024,10,202410,2024,1,0,0
04/06/2024,10,202410,2024,1,0,0
03/06/2024,10,202410,2024,1,0,0
16/06/2024,11,202411,2024,1,0,0
15/06/2024,11,202411,2024,1,0,0
14/06/2024,11,202411,2024,1,0,0
13/06/2024,11,202411,2024,1,0,0
12/06/2024,11,202411,2024,1,0,0
11/06/2024,11,202411,2024,1,0,0
10/06/2024,11,202411,2024,1,0,0
23/06/2024,12,202412,2024,1,0,0
22/06/2024,12,202412,2024,1,0,0
21/06/2024,12,202412,2024,1,0,0
20/06/2024,12,202412,2024,1,0,0
19/06/2024,12,202412,2024,1,0,0
18/06/2024,12,202412,2024,1,0,0
17/06/2024,12,202412,2024,1,0,0
30/06/2024,13,202413,2024,1,0,0
29/06/2024,13,202413,2024,1,0,0
28/06/2024,13,202413,2024,1,0,0
27/06/2024,13,202413,2024,1,0,0
26/06/2024,13,202413,2024,1,0,0
25/06/2024,13,202413,2024,1,0,0
24/06/2024,13,202413,2024,1,0,0
07/07/2024,14,202414,2024,1,0,0
06/07/2024,14,202414,2024,1,0,0
05/07/2024,14,202414,2024,1,0,0
04/07/2024,14,202414,2024,1,0,0
03/07/2024,14,202414,2024,1,0,0
02/07/2024,14,202414,2024,1,0,0
01/07/2024,14,202414,2024,1,0,0
14/07/2024,15,202415,2024,1,0,0
13/07/2024,15,202415,2024,1,0,0
12/07/2024,15,202415,2024,1,0,0
11/07/2024,15,202415,2024,1,0,0
10/07/2024,15,202415,2024,1,0,0
09/07/2024,15,202415,2024,1,0,0
08/07/2024,15,202415,2024,1,0,0
21/07/2024,16,202416,2024,1,0,0
20/07/2024,16,202416,2024,1,0,0
19/07/2024,16,202416,2024,1,0,0
18/07/2024,16,202416,2024,1,0,0
17/07/2024,16,202416,2024,1,0,0
16/07/2024,16,202416,2024,1,0,0
15/07/2024,16,202416,2024,1,0,0
28/07/2024,17,202417,2024,1,0,0
27/07/2024,17,202417,2024,1,0,0
26/07/2024,17,202417,2024,1,0,0
25/07/2024,17,202417,2024,1,0,0
24/07/2024,17,202417,2024,1,0,0
23/07/2024,17,202417,2024,1,0,0
22/07/2024,17,202417,2024,1,0,0
04/08/2024,18,202418,2024,1,0,0
03/08/2024,18,202418,2024,1,0,0
02/08/2024,18,202418,2024,1,0,0
01/08/2024,18,202418,2024,1,0,0
31/07/2024,18,202418,2024,1,0,0
30/07/2024,18,202418,2024,1,0,0
29/07/2024,18,202418,2024,1,0,0
11/08/2024,19,202419,2024,1,0,0
10/08/2024,19,202419,2024,1,0,0
09/08/2024,19,202419,2024,1,0,0
08/08/2024,19,202419,2024,1,0,0
07/08/2024,19,202419,2024,1,0,0
06/08/2024,19,202419,2024,1,0,0

Sales

CustID,Total_Sales,Week Start
E16,834,01/04/2023
F73,302,01/04/2023
F73,885,03/04/2023
E16,505,10/04/2023
F73,256,10/04/2023
E16,928,17/04/2023
F73,627,17/04/2023
F73,594,24/04/2023
F73,439,01/05/2023
E16,876,08/05/2023
F73,327,08/05/2023
E16,970,15/05/2023
F73,694,15/05/2023
F73,777,22/05/2023
F73,950,29/05/2023
E16,516,05/06/2023
F73,573,05/06/2023
E16,226,12/06/2023
F73,799,12/06/2023
F73,403,19/06/2023
F73,579,26/06/2023
E16,741,03/07/2023
F73,327,03/07/2023
E16,873,10/07/2023
F73,759,10/07/2023
F73,746,17/07/2023
F73,387,24/07/2023
E16,551,31/07/2023
F73,565,31/07/2023
F73,475,07/08/2023
F73,276,14/08/2023
F73,424,21/08/2023
E16,842,28/08/2023
F73,649,28/08/2023
E16,976,04/09/2023
F73,927,04/09/2023
F73,260,11/09/2023
F73,221,18/09/2023
E16,885,25/09/2023
F73,886,25/09/2023
F73,339,02/10/2023
E16,438,09/10/2023
F73,982,09/10/2023
E16,765,16/10/2023
F73,307,16/10/2023
E16,828,23/10/2023
F73,355,23/10/2023
F73,360,30/10/2023
F73,566,06/11/2023
E16,353,13/11/2023
F73,685,13/11/2023
F73,859,20/11/2023
F73,761,27/11/2023
F73,640,04/12/2023
F73,946,11/12/2023
E16,268,18/12/2023
F73,470,18/12/2023
F73,227,25/12/2023
E16,237,01/01/2024
F73,656,01/01/2024
F73,225,08/01/2024
F73,797,15/01/2024
F73,684,22/01/2024
E16,993,29/01/2024
F73,603,29/01/2024
F73,923,05/02/2024
F73,323,12/02/2024
F73,775,19/02/2024
F73,425,26/02/2024
F73,300,04/03/2024
F73,453,11/03/2024
F73,227,18/03/2024
F73,317,01/04/2024
F73,719,08/04/2024
F73,408,15/04/2024
F73,305,22/04/2024
F73,261,29/04/2024
F73,734,06/05/2024
E16,852,13/05/2024
F73,937,13/05/2024
E16,986,20/05/2024
F73,616,20/05/2024
F73,572,27/05/2024

Phasing

Week,Phasing
1,0.802537245
2,1
3,1
4,1
5,1.071628893
6,0.81525305
7,1
8,1.071628893
9,0.81525305
10,1
11,1
12,1
13,1
14,1
15,0.984438135
16,0.984438135
17,0.984438135
18,0.984438135
19,0.984438135
20,0.984438135
21,1.071628893
22,0.81525305
23,1
24,1
25,1
26,1
27,1
28,1
29,1
30,1
31,1
32,1
33,1
34,1.015649256

Please let me know if this doesn't work/help out at all. The measure named [Total Sales] in the original text is just a Sum of the Total_Sales: SUM(Sales[Total_Sales]).

Anonymous · ‎07-15-2024

Hi,@OldOak123

I would be grateful if you could provide me with the pbix file or sample data.SharePoint and OneDrive are recommended.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

Best Regards,

Leroy Lu

Taking a Cumulative total of a Measure Derived from a Virtual Table/SUMX

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

Join us at FabCon Vienna from September 15-18, 2025

Taking a Cumulative total of a Measure Derived from a Virtual Table/SUMX

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025