Measure for revenue decreasing over time

[Anonymous]

 Hi,

I am having trouble with presenting decreasing amount of incoming revenue over time.

Here is input dummy data that illustrates my data:

Project	Revenue	Start	End	Margin	Months	Monthly Revenue	Monthly Margin	Type
abc	10000	1.1.2018	31.12.2018	1000	12	833.33	83.33	Old
xxx	15000	1.2.2018	31.5.2019	2500	16	937.50	156.25	New
xyz	28000	1.6.2018	31.12.2019	5500	19	1473.68	289.47	New

The aim is to show how the remaining revenue decreases over time.  I.e. for project "abc" the starting point would be 10 000 and each month 833 would be deducted. The end month would then give a value of 833 (it could also be done to show 10 000 - 833 in the first month and 0 in the last month).

Like this (done in excel):

Project       Month   Revenue left  Margin left

abc	01 2018	10000.00	1000.00
abc	02 2018	9166.67	916.67
abc	03 2018	8333.33	833.33
abc	04 2018	7500.00	750.00
abc	05 2018	6666.67	666.67
abc	06 2018	5833.33	583.33
abc	07 2018	5000.00	500.00
abc	08 2018	4166.67	416.67
abc	09 2018	3333.33	333.33
abc	10 2018	2500.00	250.00
abc	11 2018	1666.67	166.67
abc	12 2018	833.33	83.33

Here's what I'm looking for in the graph:

I've tried expanding the input table months for each project and then deducting cumulative revenue from the original revenue. It works when I handle the projects separately, but as soon as I aggregate the data, the measure does something weird.

Here's the Dax I use:

Revenue Accumulation = CALCULATE(
SUM('Project Table Expanded'[Monthly Revenue]);
DATESBETWEEN(
'Project Table Expanded'[MonthYear];
MINX(ALL('Project Table Expanded'[MonthYear]);'Project Table Expanded'[MonthYear]);MAX('Project Table Expanded'[MonthYear])))

Revenue left = SUM('Project Table Expanded'[Revenue])+SUM('Project Table Expanded'[Monthly Revenue]) - 'Project Table Expanded'[Revenue Accumulation]

The weird stuff happpens after the second project ends (months in Finnish, but in correct order - sorry about that): 

EDIT: here's the current relationships I'm working with

Here is a pbix and excel describing in more detail: dummyData

Br,

T

[AkhilAshok · Accepted solution]

There are multiple ways to do this. One option is without using Project Expanded table (bigger DAX measures, formula engine driven, can get slow bigger dataset) and another is using Project Expanded table (simpler DAX, storage engine driven, fast for bigger dataset).

Since your dataset is pretty small, it doesn't matter which approach you use. But, I will explain the option using Project Expanded since that is what you already tried. Below is the approach I followed - started with your Project Table.

1. Create a Month table like below and sort the Year Month column using Year Month Number

Month =
GROUPBY (
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Project Table'[Start] ), MAX ( 'Project Table'[End] ) ),
        "Year Month", FORMAT ( [Date], "mmm yy" ),
        "Year Month Number", YEAR ( [Date] ) * 12
            + MONTH ( [Date] )
            - 1
    ),
    [Year Month],
    [Year Month Number],
    "Month Start", MINX ( CURRENTGROUP (), [Date] )
)

2. Create a new table Project Table Expanded (since you said you created yours in Excel - just to show we can do everything in Power BI itself )

Project Table Expanded = 
GENERATE (
    'Project Table',
    DATESBETWEEN ( 'Month'[Month Start], 'Project Table'[Start], 'Project Table'[End] )
)

3. Create a Relatioship between Project Table Expanded & Month using Month Start column.

4. Create the following Measures:

Total Revenue = SUM('Project Table Expanded'[Revenue])

Monthly Revenue RT =
CALCULATE (
    SUM ( 'Project Table Expanded'[Monthly Revenue] ),
    FILTER ( ALL ( 'Month' ), 'Month'[Month Start] < MAX ( 'Month'[Month Start] ) )
)

Revenue Left =
SUMX (
    VALUES ( 'Project Table Expanded'[Project] ),
    [Total Revenue] - [Monthly Revenue RT]
)

5. Create your Chart using the Month[Year Month], 'Project Table Expanded'[Type] and [Revenue Left]

Let me know if it works for you.

[Anonymous]

Thank you @AkhilAshok!

I'll give this a go and tell you how it works.

Thanks for using the Project Expanded - the real dataset has 500 projects instead of 3.

Br,

T