CODEWITHSUNDEEP
powerbi
Tom Winchester
Tom Winchester

Reputation: 397

PowerBI Adjusted Cumulative Total Measure

I've got a measure called _CumulativeBudget that has the following formula:

_CumulativeBudget = CALCULATE(SUM('Sheet1'[2017 Budget]), FILTER(ALLSELECTED('Sheet1'), 'Sheet1'[Imp Month]<=MAX('Sheet1'[Imp Month])))

Which you can see as the line in the following chart:

_CumulativeBudget Example

The problem is that each of those bars represent the END of a cash flow. For example, there is a project that was allocated $0.2M in budget whose "Imp Month" (Impact Month) is March. But really, the team was given all $0.2M at the beginning of the year and probably spent about 1/3 of that money in Jan, 1/3 in Feb, and 1/3 in Mar.

So if a project has an Imp Month of X, I'd like the cumulative measure to sum 1/X of it's budget in Jan, 1/X in Feb, etc.

Is this something that's fairly doable in a measure? If not, I may just go with a straight line approximation--e.g. if the year-end number is $1.9M then the budget is approximately 1/12th of that each month.

Thanks in advance for any assistance, guys!

Upvotes: 1

Views: 2379

Answers (1)

Joe G
Joe G

Reputation: 1776

I started with some mock data that looks like this.

Mock data

1) I created a table of months.

Months = SELECTCOLUMNS(
    FILTER(
        CALENDAR(DATE(2017, 1, 1), DATE(2017, 12, 31)),
        DAY([Date]) = 1
    ),
    "Month", [Date]
)

Month Table

2) I created a calculated table that crossjoined the two tables and filtered it down based on comparing Imp Month to the Month from the month table. At the same time, I limited down the columns and created a new column for the monthly budget.

BudgetExpanded = SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(Sheet1, Months),
        YEAR(Sheet1[Imp Month]) = Months[Month].[Year] &&
        MONTH(Sheet1[Imp Month]) >= Months[Month].[MonthNo]
    ),
    "Project", Sheet1[Project],
    "Imp Month", Months[Month].[Date],
    "Monthly Budget", CALCULATE(
        MAX(Sheet1[2017 Budget]) / MONTH(MAX(Sheet1[Imp Month])),
        FILTER(Sheet1,
            Sheet1[Project] = EARLIER(Sheet1[Project])
        )
    )
)

Calculated Table

3) I created relationships between the tables.

Relationships

4) I added your cumulative measure to the new table and updated it for the Monthly Budget column.

CumulativeBudget = CALCULATE(
    SUM(BudgetExpanded[Monthly Budget]),
    FILTER(
        ALLSELECTED(BudgetExpanded), 
        BudgetExpanded[Imp Month] <= MAX(BudgetExpanded[Imp Month])
    )
)

New Measure

5) I created a combo chart similar to yours with the original 2017 Budget and the new CumulativeBudget (black line is the new measure; red line is your original measure).

Visual

Upvotes: 2

Related Questions