Good day to you all,
I'm really struggling with trying to find the correct dax to use to break down a forecast pipeline into yearly amounts. We have multiple projects that start and end at different times. Please see below an example of how I need it to look in a table within Power Bi.
The columns without formatting is the information I already have and the blue shaded area is what I need to replicate.
Any advice would be much appreciated
Thanks, Jake
*Construction Length and Management Length are in months
Solved! Go to Solution.
Hi @jakesolty,
I try to reproduce your scenario, please create the calculated column use the following formula.
2017 = IF ( YEAR ( DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) ) ) = 2017, Test[Construction Length] * Test[Construction p/m], IF ( YEAR ( Test[Start of Construction] ) = 2017, IF ( ( DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH ) <= Test[Construction Length] ), DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH ) * Test[Construction p/m], BLANK () ), BLANK () ) + IF ( YEAR ( Test[Start of Management] ) = 2017, IF ( ( DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH ) <= Test[Management Length] ), DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH ) * Test[Management p/m], BLANK () ), BLANK () ) )
2018 = IF ( 2018 < YEAR ( DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) ) ), IF ( YEAR ( Test[Start of Construction] ) = 2017, IF ( MONTH ( Test[Start of Construction] ) = 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) ) * Test[Construction p/m] - Test[2017], IF ( MONTH ( Test[Start of Construction] ) = 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) ) * Test[Construction p/m] ), BLANK () )
For 2019 and 2019+, you can use the similar method to get.
Best Regards,
Angelia
Hi @jakesolty,
I try to reproduce your scenario, please create the calculated column use the following formula.
2017 = IF ( YEAR ( DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) ) ) = 2017, Test[Construction Length] * Test[Construction p/m], IF ( YEAR ( Test[Start of Construction] ) = 2017, IF ( ( DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH ) <= Test[Construction Length] ), DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH ) * Test[Construction p/m], BLANK () ), BLANK () ) + IF ( YEAR ( Test[Start of Management] ) = 2017, IF ( ( DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH ) <= Test[Management Length] ), DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH ) * Test[Management p/m], BLANK () ), BLANK () ) )
2018 = IF ( 2018 < YEAR ( DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) ) ), IF ( YEAR ( Test[Start of Construction] ) = 2017, IF ( MONTH ( Test[Start of Construction] ) = 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) ) * Test[Construction p/m] - Test[2017], IF ( MONTH ( Test[Start of Construction] ) = 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1, DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) ) * Test[Construction p/m] ), BLANK () )
For 2019 and 2019+, you can use the similar method to get.
Best Regards,
Angelia
Thanks for the speedy response, I will check this now.