Calculate revenue from a table based on a date in another column

[yaabandoh]

I have data structured this way:

Branch ID	Commencement Date	Status	Area	Date	Revenue
0030		Old Branch	Red Avenue	01-2019	5,000
0021		Old Branch	Red Avenue	01-2019	2,500
0053		Old Branch	Green Lane	01-2019	5,632
0078		Old Branch	Green Lane	01-2019	5,471
0030		Old Branch	Red Avenue	02-2019	5,000
0021		Old Branch	Red Avenue	02-2019	2,500
0025	2/30/2019	New Branch	Red Avenue	02-2019	10,000
0053		Old Branch	Green Lane	02-2019	2,541
0078		Old Branch	Green Lane	02-2019	5,476
0030		Old Branch	Red Avenue	03-2019	5,000
0021		Old Branch	Red Avenue	03-2019	2,500
0025	2/30/2019	New Branch	Red Avenue	03-2019	10,000
0064	3/10/2019	New Branch	Red Avenue	03-2019	5,000
0053		Old Branch	Green Lane	03-2019	5,428
0078		Old Branch	Green Lane	03-2019	4,785
0052	4/15/2019	New Branch	Apple Groove	04-2019	6,547
0030		Old Branch	Red Avenue	04-2019	5,741
0021		Old Branch	Red Avenue	04-2019	2,500
0025	2/30/2019	New Branch	Red Avenue	04-2019	10,265
0064	3/10/2019	New Branch	Red Avenue	04-2019	5,054
0053		Old Branch	Green Lane	04-2019	3,256
0078		Old Branch	Green Lane	04-2019	8,756
0052	4/15/2019	New Branch	Apple Groove	05-2019	6,441
0030		Old Branch	Red Avenue	05-2019	5,220
0021		Old Branch	Red Avenue	05-2019	2,501
0025	2/30/2019	New Branch	Red Avenue	05-2019	4,789
0064	3/10/2019	New Branch	Red Avenue	05-2019	3,247
0053		Old Branch	Green Lane	05-2019	6,581
0078		Old Branch	Green Lane	05-2019	6,985

I need help with a measure to Calculate revenue for each area Before and after the commencement date of the first new branch.

To return in an Area Table 2 columns:

1. Total revenue per Area for all months before the month the first new branch commenced (eg. Total Revenue for Red Avenue before Feburary which is the month first new branch 0025 came up)

2. Total Revenue per Area for all months during and After the first new branch is announced (eg. Total revenue for Red Avenue for February and subsequent months- total revenue for the Red Avenue Area from the month the new branch 0025 started.

This should also be noted

1. Total Revenue for Areas only old branches (eg. Green Lane)

2. Total Revenue for Areas with only new branch (eg. Apple Groove)

[sturlaws · Accepted solution]

Hi @yaabandoh,

it would have been helpfull if you also posted what you want your outcome to look like.

Could you check if these two measures gives you what you need?

before =
SUMX (
    CALCULATETABLE (
        data;
        VAR _currentArea =
            CALCULATE ( SELECTEDVALUE ( data[Area] ) )
        VAR _firstCommencementDate =
            CALCULATE (
                MIN ( data[Commencement Date] );
                FILTER ( ALL ( data ); data[Area] = _currentArea )
            )
        VAR _firstCommenceMonth =
            DATE ( YEAR ( _firstCommencementDate ); MONTH ( _firstCommencementDate ); 1 )
        RETURN
            FILTER (
                ALL ( data );
                data[Area] = _currentArea
                    && data[Date] < _firstCommenceMonth
            )
    );
    data[Revenue]
)

on and after =
SUMX (
    CALCULATETABLE (
        data;
        VAR _currentArea =
            CALCULATE ( SELECTEDVALUE ( data[Area] ) )
        VAR _firstCommencementDate =
            CALCULATE (
                MIN ( data[Commencement Date] );
                FILTER ( ALL ( data ); data[Area] = _currentArea )
            )
        VAR _firstCommenceMonth =
            DATE ( YEAR ( _firstCommencementDate ); MONTH ( _firstCommencementDate ); 1 )
        RETURN
            FILTER (
                ALL ( data );
                data[Area] = _currentArea
                    && data[Date] >= _firstCommenceMonth
            )
    );
    data[Revenue]
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[yaabandoh]

@sturlaw additional help please.
How can I modify the measure to give average revenue per branch per area before and after the commence date

[sturlaws]

you can change SUMX with AVERAGEX is the measures, but it will not give you pr branch, if you want that as well.

If you need to have it by branch as well, could you, based on the data you originally posted, create a table in e.g. excel to show the desired output?

[yaabandoh]

@sturlaws 

My data table remains the same. I will like to get average revenue before and average revenue after. So average revenue before will have 2 steps:
1. Average revenue per month= Total revenue(as derived from the first measure)/ Number of months before first commencement date

2. Average revenue per month per branch= Average revenue per month/total Number of branches before first commencement date

average revenue after will basically be number of months after first commencement date and total number of branches after first commencement date. 

Thank you

[yaabandoh]

@sturlaws Thanks so much. Works perfectly.