Cumulative formula in Power query

[619SK]

Data in power bi i'm having is  Daily count and Monthly target 50. Here i want to calculate Target value with difference of Prev  day count. so as per below example.  June target is 50.   and 1st June count completed is 2 so target on 2nd June will be 48 . 

After acheiving i will show line chart.

Date	Actual Target	Target	Projected
01-06-2024	2	50	5
02-06-2024	10	48	5
03-06-2024	2	38	5
04-06-2024	5	36	5
05-06-2024	5	31	5
06-06-2024	10	26	5
07-06-2024	4	16	5
08-06-2024	4	12	5
09-06-2024	4	8	5
10-06-2024	4	4	5

[Anonymous · Accepted solution]

Hi @619SK ，

Thank you @Ashish_Mathur and @rajendraongole1  very much for the solution, and I've tried other ways to help you understand the problem:

We can add a COLUMN to your data to accomplish your goal.

column = 
VAR monthly = 50 

VAR  cumu =CALCULATE(SUM('Table'[Actual Target]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])
))
RETURN
monthly -cumu

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Ashish_Mathur]

Hi,

Will the opening target always be a fixed number i.e. 50 or will this come from another table?  If it will, then share that table as well.

[rajendraongole1]

Hi @619SK -I have tried to create a measure will calculate the cumulative sum of the actual daily using DAX, instead of PQ.

Measure as below:

Cumulative Actuals =
CALCULATE(
SUM('Table'[Actual Target]),
FILTER(
ALL('Table'[Date]),
'Table'[Date] <= MAX('Table'[Date])
)
)

create another measure that will calculate the remaining target by subtracting the cumulative actuals

Remaining Target =
VAR MonthlyTarget = 50 
RETURN
MonthlyTarget - [Cumulative Actuals]

as you mentioned a fixed value of 5, so create another constant measure.

Projected = 5

add it in your line chart and check the cumulative dynamically calculate the remaining target.

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!