Forum Discussion

619SK's avatar
619SK
Helper II
2 years ago
Solved

Cumulative formula in Power query

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.

 

 

DateActual TargetTargetProjected
01-06-20242505
02-06-202410485
03-06-20242385
04-06-20245365
05-06-20245315
06-06-202410265
07-06-20244165
08-06-20244125
09-06-2024485
10-06-2024445
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.