Issue with accumulated amounts

[gomezc73]

Hi Expert,

I need help with the following problem, any idea on how to solve it would be appreciated.

I have a table that has an amount per day, so I made a calculation to generate an accumulated amount . Something like this.

Product	Month	DATE	AMOUNT	Week Day	Daily Accumulated
00237788	Jan	01/01/2023	100	Sun	100
00237788	Jan	01/02/2023	341	Mon	441
00237788	Jan	01/03/2023	728	Tue	1,169
00237788	Jan	01/04/2023	316	Wed	1,485
00237788	Jan	01/05/2023	(159)	Thu	1,326
00237788	Jan	01/06/2023	(743)	Fri	583
00237788	Jan	01/07/2023	150	Sat	733
00237788	Jan	01/08/2023	(67)	Sun	666
00237788	Jan	01/09/2023	(134)	Mon	532
00237788	Jan	01/10/2023	(69)	Tue	463
00237788	Jan	01/11/2023	(25)	Wed	438
00237788	Jan	01/12/2023	(66)	Thu	372
00237788	Jan	01/13/2023	799	Fri	1,171
00237788	Jan	01/14/2023	351	Sat	1,522
00237788	Jan	01/15/2023	170	Sun	1,692
00237788	Jan	01/16/2023	36	Mon	1,728
00237788	Jan	01/17/2023	(49)	Tue	1,679
00237788	Jan	01/18/2023	(34)	Wed	1,645
00237788	Jan	01/19/2023	(70)	Thu	1,575
00237788	Jan	01/20/2023	(62)	Fri	1,513
00237788	Jan	01/21/2023	142	Sat	1,655
00237788	Jan	01/22/2023	850	Sun	2,505
00237788	Jan	01/23/2023	(13)	Mon	2,492
00237788	Jan	01/24/2023	111	Tue	2,603
00237788	Jan	01/25/2023	(386)	Wed	2,217
00237788	Jan	01/26/2023	(57)	Thu	2,160
00237788	Jan	01/27/2023	737	Fri	2,897
00237788	Jan	01/28/2023	22	Sat	2,919
00237788	Jan	01/29/2023	179	Sun	3,098
00237788	Jan	01/30/2023	(91)	Mon	3,007
00237788	Jan	01/31/2023	13	Tue	3,020

Now, the user wants  a line chart, but he wants to select a day of the week (Monday, Tuesday, etc.) and when I select a week day (By example TUESDAY), the formulas are only accumulating the selected day.

Let me explain, if I select Tuesday, the formula only accumulate the amounts for Tuesdays and what I need is for it to add up all the days but only graph the total for Tuesdays.

By example when i select TUESDAY, the day 01/03/2023 only accumulate 728.

When i need accumulate the days:

01/01 : 100 (Sunday)

01/02 : 341 (Monday)

01/03 : 728 (Tuesday)

TOTAL: 1169 (this is the correct amount to display)

Product	Month	DATE	AMOUNT	Week Day	Wrong Daily Accumulated	Correct Daily Accumulated
00237788	Jan	01/03/2023	728	Tue	728	1,169
00237788	Jan	01/10/2023	(69)	Tue	659	463
00237788	Jan	01/17/2023	(49)	Tue	610	1,679
00237788	Jan	01/24/2023	111	Tue	721	2,603
00237788	Jan	01/31/2023	13	Tue	734	3,020

Thanks in advance

[Anonymous · Accepted solution]

Hi @gomezc73 ,
Thanks for rajendraongole1 reply.
You can create a calculate column

Daily Accumulated  = 
VAR _currentDate = 'Table'[DATE]
RETURN
CALCULATE(
    SUM('Table'[AMOUNT]),
    FILTER(
        'Table',
        'Table'[DATE] <= _currentDate
    )
)

Then create a line chart

Best regards,
Albert He

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

[gomezc73]

It works!!. thank you very Much!!

[rajendraongole1]

Hi @gomezc73 -  you need a measure in Power BI that calculates the correct daily accumulated amount while respecting the user's weekday selection.

Create a measure for the total running accumulation

Total Running Accumulated =
CALCULATE(
SUM('Table'[AMOUNT]),
FILTER(
ALL('Table'),
'Table'[DATE] <= MAX('Table'[DATE])
)
)

Create another  measure to filter for the selected weekday

Correct Daily Accumulated =
IF(
MAX('Table'[Week Day]) IN VALUES('Table'[Week Day]),
CALCULATE(
SUM('Table'[AMOUNT]),
FILTER(
ALL('Table'),
'Table'[DATE] <= MAX('Table'[DATE])
)
)
)

Now, add a slicer for the Week Day column: Use the Week Day column as a slicer so users can select the desired weekday.

use a line chart:

Add the DATE column to the X-axis.
Use the Correct Daily Accumulated measure for the Y-axis. Hope this works.

[gomezc73]

Hi, thank you for your early response. I tried your solution but i am still showing wrong amounts.

Is it possible for you create a PBI and send me it?. maybe i am making something wrong.

thanks in advance