Cumulative total for non-date columns stop at the last value

[imdshk]

Hi all,

I am trying to calculate cumulative total for the costs incured during the projects lifetime, but visualizing it based on the number of weeks to or past the deadline. I am having trouble stopping the cummulative total at the last value of that project. There are two data tables in the model

1. Cost_Data (data of all the cost incurred cost across projects)

2. Project_Data (project deadline date)

The Non-Cumulative Sum looks like this 

Non Cumulative Sum

I created a calculated table Week_Lookup to get unique week numbers before/after deadline and the following cumulative measure

Cummulative Cost_Week = 

IF ( MIN ( Week_Lookup[Weeks Before Deadline] ) <= CALCULATE ( MAX ( Cost_Data[Weeks Before Deadline] ), ALL ( Cost_Data ) ),
    CALCULATE ( 
              SUM ( Cost_Data[Cost] ),
              FILTER(
                    ALL ( Week_Lookup ),
                    Week_Lookup[Weeks Before Deadline] <= MAX ( Week_Lookup[Weeks Before Deadline] )
               )
       ), BLANK()
)

When I use the measure in the table, the cumulative sum looks as below, which does not stop at the last available cost value of the week (it should stop before the red highlighted values), for instance in P3, the cumulative sum shall stop at -1 week (which has the last cost data), but it continues until the end. This behaivour is seen in other columns as well (P5 should be till -2 week etc.,) . Could anyone suggest a solution / alternative for this issue.

Cumulative Sum - Wrong

Link to Power BI File:  https://1drv.ms/u/s!Ak7F5eFJkBTfgq4JsDXCKk_GTACI8Q?e=9Q47Sx

Thanks 🙂

[parry2k · Accepted solution]

@imdshk update your measure as below

Cummulative Cost_Week = 
VAR __maxProjectWeek = CALCULATE( MAX ( Cost_Data[Weeks Before Deadline] ), ALL ( Week_Lookup[Weeks Before Deadline] ) )
VAR __flag = IF ( SELECTEDVALUE ( Week_Lookup[Weeks Before Deadline] ) <= __maxProjectWeek, 1 )
RETURN

IF ( MIN ( Week_Lookup[Weeks Before Deadline] ) <= CALCULATE ( MAX ( Cost_Data[Weeks Before Deadline] ), ALL ( Cost_Data ) ),
    CALCULATE ( 
        SUM ( Cost_Data[Cost] ),
        FILTER(
            ALL ( Week_Lookup ),
            Week_Lookup[Weeks Before Deadline] <= MAX ( Week_Lookup[Weeks Before Deadline] )
        )
    ), BLANK()
) * DIVIDE ( __flag, __flag )

and here is the output

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[imdshk]

@parry2k  Thank you. This solves the issue 🙂