Add 6 months to project start date and sum revenue values within/outside that project

[Anonymous]

Hi all,

I'm trying to add 6 months to a project start date and then sum the values that fell within the project start/end date, as well as summing those which fell outside the dates.

I tried to first work out the project end date but I keep getting errors telling me that there are multiple values being supplied... this was my syntax:

PricProj Date +6 Mths = IF(ISFILTERED(MD_Sites[Site_Name]),
                            (DATEADD(MD_Sites[Pricing_Project_Date].[Date], +6, MONTH)),
                            "Select a Site")

I have a site table with the name, ID etc of the individual site, as well as it's specific project start date. These could be the same or different for each individual site. There are also a number of sites with no project start date.

The project end date should always be 6 months after it started.

My attempt at the sum was as follows:

Authorised 13D-25 Rev = 
VAR Pricing_Project_Date = CALCULATE(FIRSTDATE(MD_Sites[Pricing_Project_Date]))
VAR Pricing_project_6Mths = CALCULATE(DATEADD(MD_Sites[Pricing_Project_Date], +6, MONTH))
RETURN
CALCULATE(SUMX(REV_Revenue,REV_Revenue[Fee]),
                            FILTER(REV_Revenue,
                            REV_Revenue[TransactionDate] >= Pricing_Project_Date &&
                            REV_Revenue[TransactionDate] <= Pricing_project_6Mths
                            )
)

But I just kept getting blank...

Any ideas?

Cheers,

Aaron

[Mariusz]

Hi @Anonymous 

Not sure if I understand your requierment, but have a go at this.

Authorised 13D-25 Rev = 
CALCULATE(
    SUM( REV_Revenue[Fee] ),
    DATEADD( MD_Sites[Pricing_Project_Date].[Date], 6, MONTH )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz 

Not quite what I'm looking for but thanks for the reply! You're always very responsive! I actually think I've got a little closer with this measure:

Unauthorised 13D-40 Rev = 
CALCULATE(
    [13D-40 Revenue],
        FILTER(REV_Revenue,
        REV_Revenue[TransactionDate] > (RELATED('MD_Sites'[Pricing_Project_Date])+180)
        )
)

This essentially gets me the correct value I want, but isn't quite as accurate as I'd like it to be. Obviously the assumtion that 180 days is 6 months isn't quite right.

I tried to build the DATEADD function into the RELATED function but it doesn't allow it.

My logic is slightly different in this example from the original post. This time I'm trying to evaluate the sum of revenue from 6 months after the project start date.

Any thoughts?

Cheers,

Aaron

[Mariusz]

Hi @Anonymous 

I thought you should be able to nest RELATED within DATEADD, is it not working for you?

Unauthorised 13D-40 Rev = 
CALCULATE(
    [13D-40 Revenue],
    FILTER(
        REV_Revenue,
        REV_Revenue[TransactionDate] > DATEADD( RELATED('MD_Sites'[Pricing_Project_Date]), 6, MONTH)
    )
)

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz & @v-piga-msft ,

Perhaps this is easier to explain with some sample dummy data. Take these tables for example:

Region Table

Area ID	Region Name	Area Name
1	Region 1	Area 1
2	Region 1	Area 2

Practice Table

Practice ID	Practice Name
1	Practice 1
2	Practice 2
3	Practice 3
4	Practice 4

Site Table

Site ID	Site Name	Area ID	Practice ID	Pricing Project Start Date
1	Site 1	1	1	10/11/2018
2	Site 2	1	2	12/12/2018
3	Site 3	2	3	15/09/2018
4	Site 4	2	4
5	Site 5	2	4	01/02/2019

The Site Table maps to both the region and practice table on their respective "ID" columns. The region is obviously the highest level of the heirarchy.

I also have my revenue table:

Transaction Date	Site ID	Value
08/10/2018	1	70
20/10/2018	4	82
07/11/2018	5	46
08/11/2018	1	22
08/11/2018	2	62
03/12/2018	4	45
03/12/2018	3	84
07/12/2018	3	25
17/12/2018	1	96
25/12/2018	2	18
28/12/2018	4	31
04/01/2019	2	12
04/01/2019	1	89
07/01/2019	2	96
28/01/2019	2	76
01/02/2019	4	48
07/02/2019	2	43
18/02/2019	4	56
27/03/2019	3	16
06/04/2019	4	52
26/05/2019	4	100
07/06/2019	2	52
31/07/2019	5	90
01/10/2019	2	43
15/10/2019	4	14
21/10/2019	1	11
13/11/2019	3	80
18/11/2019	4	86
20/11/2019	5	21
04/12/2019	1	87
07/12/2019	3	80

I want to be able to calculate the sum of revenue that was gained 6 months after the site pricing project start date. This would mean that each site will have to be calculated individually, as their pricing project start dates are different (or may not even have one). This should then roll up to the practice, area and region level.

At the moment, my current formula (below) only evaluates one pricing project date and applies it to all the sites. Therefore, when one site is filtered, the calculation is correct, however when a practice (or higher level in the heirarchy) is selected, the result is wrong.

Unauthorised 13D-40 Rev = 
CALCULATE(
    [13D-40 Revenue],
        FILTER(REV_Revenue,
        REV_Revenue[TransactionDate] > (RELATED('MD_Sites'[Pricing_Project_Date])+180)
        )
)

Is there a way to have this calculation evaluate each individual site, then roll it up to the other levels?

For example the aggregated results based on my dummy data should be:

Cheers,

Aaron

[Mariusz · Accepted solution]

Hi @Anonymous 

You could add a column to your site table to calculate your +6m, like below.

Pricing Project Start Date +6m = DATEADD( Site[Pricing Project Start Date].[Date], 6, MONTH )

 Then you should be able to use the measure below.

Measure = 
CALCULATE(
    SUM( 'Transaction'[Value] ) + 0,
    ISBLANK(Site[Pricing Project Start Date +6m].[Date]) = FALSE(),
    FILTER(
        'Transaction',
        'Transaction'[Transaction Date].[Date] >  RELATED( Site[Pricing Project Start Date +6m].[Date] )
    )
)

Please see my screenshot  below with your desired results.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

@Mariusz ,

Apologies for the delay getting back to you - was just testing that this solution works and it does!

Thanks very much - I really appreciate it!

Aaron

[Mariusz]

HI @Anonymous 

No problem, always happy to help!

Best Regards,
Mariusz

Please feel free to connect with me.