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.
Hello,
I am trying to CALCULATETABLE (sample below) to iterate through the FIRSTDATE (START_DATE) and LASTDATE (END_DATE) and straight-line REVENUE and LABOR over each monthly period so I can tie the proposed project data to monthly actuals to a date dimension table.
PROPOSED PROJECT DATA
PROJ_ID START_DATE END_DATE REVENUE LABOR
11111 8/15/2020 8/14/2021 1,000,000 750,000
11111 8/15/2021 8/14/2022 1,000,000 750,000
11112 8/15/2020 8/14/2021 1,000,000 750,000
11113 8/15/2020 8/14/2021 1,000,000 750,000
I thank you in advanced.
Solved! Go to Solution.
Hi @Anonymous
I build a table like yours and add a calculated column in it.
Rev & Labor =
VAR baseTable =
DISTINCT ( ( 'Post Award Tracking v2'[Project Code] ) )
RETURN
GENERATE (
baseTable,
VAR PoP_Start =
MIN ( 'Post Award Tracking v2'[POP Start] )
VAR PoP_End =
MAX ( 'Post Award Tracking v2'[POP End] )
VAR MonthDiff =
DATEDIFF ( PoP_Start, PoP_End, MONTH )
VAR Avg_Rev =
DIVIDE (
CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) ),
MonthDiff
)
VAR Revenue =
CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) )
VAR Avg_LABOR =
DIVIDE ( CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) ), MonthDiff )
VAR Labor =
CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) )
RETURN
GENERATE (
GENERATESERIES ( PoP_Start, PoP_End, 1 ),
VAR inc = [Value]
VAR _Month =
MONTH ( [Value] )
RETURN
ROW (
"PoP Start", PoP_Start,
"PoP End", PoP_End,
"Months", MonthDiff,
"Avg Rev", Avg_Rev,
"Revenue", Revenue,
"Avg Labor", Avg_LABOR,
"Labor", Labor,
"Month", _Month
)
)
)
Min each month date =
IF (
AND (
MONTH ( 'Rev & Labor'[Value] ) = MONTH ( 'Rev & Labor'[PoP Start] ),
YEAR ( 'Rev & Labor'[Value] ) = YEAR ( 'Rev & Labor'[PoP Start] )
),
'Rev & Labor'[PoP Start],
CALCULATE (
MIN ( 'Rev & Labor'[Value] ),
FILTER (
'Rev & Labor',
'Rev & Labor'[Month] = EARLIER ( 'Rev & Labor'[Month] )
&& YEAR ( 'Rev & Labor'[Value] ) = EARLIER ( 'Rev & Labor'[Value].[Year] )
)
) + 20
)
Build a Table Visual to show the result:
You can download the pbix file from this link: CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Dates.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
@v-rzhou-msft Thank you and it is VERY close.
One last item: In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP) Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?
As an example, project 25011 should only have 6 line items:
Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16
I appreciate all the help!
Brad
@Ashish_Mathur @v-rzhou-msft @AntrikshSharma
Using project 11113 as an example, the results would be:
PROJECT_ID PERIOD REVENUE LABOR
11113 9/18/2018 83,333 62,500
11113 10/18/2018 83,333 62,500
11113 11/17/2018 83,333 62,500
11113 12/17/2018 83,333 62,500
11113 1/16/2019 83,333 62,500
11113 2/15/2019 83,333 62,500
11113 3/17/2019 83,333 62,500
11113 4/16/2019 83,333 62,500
11113 5/16/2019 83,333 62,500
11113 6/15/2019 83,333 62,500
11113 7/15/2019 83,333 62,500
11113 8/14/2019 83,333 62,500
Hope this helps!
Hi @Anonymous
I build a table like yours to have a test.
Build a Period for 11111 table.
Build two measure:
REVENUE =
VAR _Re =
CALCULATE (
SUM ( 'PROPOSED PROJECT DATA'[REVENUE] ),
FILTER (
'PROPOSED PROJECT DATA',
'PROPOSED PROJECT DATA'[START_DATE] <= MAX ( 'Period for 11111'[Period] )
&& 'PROPOSED PROJECT DATA'[END_DATE] >= MAX ( 'Period for 11111'[Period] )
&& 'PROPOSED PROJECT DATA'[PROJ_ID] = MAX ( 'Period for 11111'[ID] )
)
)
RETURN
DIVIDE ( _Re, 12 )
LABOR =
VAR _LA =
CALCULATE (
SUM ( 'PROPOSED PROJECT DATA'[LABOR] ),
FILTER (
'PROPOSED PROJECT DATA',
'PROPOSED PROJECT DATA'[START_DATE] <= MAX ( 'Period for 11111'[Period] )
&& 'PROPOSED PROJECT DATA'[END_DATE] >= MAX ( 'Period for 11111'[Period] )
&& 'PROPOSED PROJECT DATA'[PROJ_ID] = MAX ( 'Period for 11111'[ID] )
)
)
RETURN
DIVIDE ( _LA, 12 )
Result:
You can download the pbix file from this link: CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Date
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft I'm close! The below code is skipping a month. It should iterate over a 60 month period but I'm getting 31. The average revenue over the table equals total revenue! You have been a great help/inspiration. All your help has been greatly appreciated.
CLOSE =
VAR baseTable =
DISTINCT( ( 'Post Award Tracking v2'[Project Code] ) )
RETURN
GENERATE (
baseTable,
VAR PoP_Start = [PoP_Start] //MIN
VAR PoP_End = [PoP_End] //MAX
VAR Months =
DATEDIFF ( PoP_Start, PoP_End, MONTH ) -- calc number of months for the project
VAR Months_Offset =
DATEDIFF ( PoP_Start, PoP_End, MONTH ) -- didn't work
VAR Avg_Rev =
DIVIDE ( CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue] ) ), Months )
VAR Revenue =
CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue] ) )
RETURN
GENERATE (
GENERATESERIES ( PoP_Start, PoP_End,Months ), -- this is the part I'm having trouble with, skipping a month
VAR inc = [Value]
RETURN
ROW (
//"Proj_ID", baseTable,
"PoP Start", PoP_Start,
"PoP End", PoP_End,
"Months", Months,
"Avg Rev", Avg_Rev,
"Revenue", Revenue
)
)
)
Result:
(can't figure out how to add images :()
Hi @Anonymous
I build a table like yours and add a calculated column in it.
Rev & Labor =
VAR baseTable =
DISTINCT ( ( 'Post Award Tracking v2'[Project Code] ) )
RETURN
GENERATE (
baseTable,
VAR PoP_Start =
MIN ( 'Post Award Tracking v2'[POP Start] )
VAR PoP_End =
MAX ( 'Post Award Tracking v2'[POP End] )
VAR MonthDiff =
DATEDIFF ( PoP_Start, PoP_End, MONTH )
VAR Avg_Rev =
DIVIDE (
CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) ),
MonthDiff
)
VAR Revenue =
CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) )
VAR Avg_LABOR =
DIVIDE ( CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) ), MonthDiff )
VAR Labor =
CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) )
RETURN
GENERATE (
GENERATESERIES ( PoP_Start, PoP_End, 1 ),
VAR inc = [Value]
VAR _Month =
MONTH ( [Value] )
RETURN
ROW (
"PoP Start", PoP_Start,
"PoP End", PoP_End,
"Months", MonthDiff,
"Avg Rev", Avg_Rev,
"Revenue", Revenue,
"Avg Labor", Avg_LABOR,
"Labor", Labor,
"Month", _Month
)
)
)
Min each month date =
IF (
AND (
MONTH ( 'Rev & Labor'[Value] ) = MONTH ( 'Rev & Labor'[PoP Start] ),
YEAR ( 'Rev & Labor'[Value] ) = YEAR ( 'Rev & Labor'[PoP Start] )
),
'Rev & Labor'[PoP Start],
CALCULATE (
MIN ( 'Rev & Labor'[Value] ),
FILTER (
'Rev & Labor',
'Rev & Labor'[Month] = EARLIER ( 'Rev & Labor'[Month] )
&& YEAR ( 'Rev & Labor'[Value] ) = EARLIER ( 'Rev & Labor'[Value].[Year] )
)
) + 20
)
Build a Table Visual to show the result:
You can download the pbix file from this link: CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Dates.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-rzhou-msft!
One last item: In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP) Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?
As an example, project 25011 should only have 6 line items:
Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16
I appriciate all the help!
Brad
Thank you so much! It is close... Is there a way to do it dynamically? I know it’s kind of sloppy, but the attempted code is below. Can I use the GENERATESERIES based on start and end columns? Also, rather than measures, I would like to calculate a table for others to use.
Sample
Project Code Project Name POP Start POP End Revenue_TEST Labor_TEST
15147 SATCOM San 7/21/2014 7/20/2015 $1,000,000 $750,000
11139 WAPA Dept Of 10/1/2014 9/30/2015 $1,000,000 $750,000
19031 NAVAIR 7.0 2/16/2015 2/15/2016 $1,000,000 $750,000
15160 Kwaj Security and 2/28/2015 2/15/2016 $1,000,000 $750,000
19032 Logistics Analysis 3/9/2015 3/8/2016 $1,000,000 $750,000
25011 Oak Ridge - DOE 5/1/2015 11/30/2015 $1,000,000 $750,000
36007 NASA Lab Services 5/1/2015 9/30/2016 $1,000,000 $750,000
24005 Fusion Center IDS 7/1/2015 6/30/2016 $1,000,000 $750,000
TEST =
VAR baseTable =
CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Post Award Tracking v2'[Project Code] ) )
RETURN
GENERATE (
baseTable,
VAR Months =
DATEDIFF ( [POP Start Measure], [POP End Measure], DAY )
VAR Start_Date = [POP Start Measure]
VAR End_Date = [POP End Measure]
VAR AvgRev =
DIVIDE ( [Revenue_Measure], Months, 0 )
VAR Revenue = [Revenue_Measure] //VAR GenSeries = GENERATESERIES([POP Start Measure],[POP End Measure],Months)
RETURN
ROW (
"Start", Start_Date,
"End", End_Date,
"Months", Months,
"Avg Rev", AvgRev,
"Revenue", Revenue
)
)
Thank you so much for your help!
Hi,
I cannot understand your result. How have you arrived at the Date in the Period column and the numbers in the last 2 columns?
Hi,
Show the expected result with an explanation.
Hi @Anonymous
Could you provide me more details about your calculate logic? You may show me the result you want the table you may need to use and the relationships you build. Or you can share me your pbix file by your Onedrive for Business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |