Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 ) )
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
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) ) )
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
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |