How to get the Annual Benefit value for the left over months?

[Anonymous]

Calculation :

Assume a project has a “Total Benefit” per year of £120. The following table shows how to chart the annual benefit depending on the project completion date. Please help me on the DAX formula in achieving the annual benefits for the years 2022 to 2026.

Project completion date	2022	2023	2024	2025	2026	Notes
30Jun22	£60	£120	£120	£120	£120	Benefits start in July 22 = Six months of benefit in 2022 = (£120/12) x 6 = £60
31Oct22	£20	£120	£120	£120	£120	Benefits start in November 22 = 2 months of benefit in 2022 = (£120/12) x2  = £20
31Dec22	£0	£120	£120	£120	£120	Benefits start in January 23 = 12 months of benefit in 2023 = £120
31Mar23	£0	£90	£120	£120	£120	Benefits start in March 23 = 9 months of benefit in 2023 = (£120/12) x9  = £90
31Oct23	£0	£20	£120	£120	£120	Benefits start in November 23 = 2 months of benefit in 2023 = (£120/12) x2  = £20

Please help me in providing the DAX for the following requirement. It would be really helpful.

Thanks in advance !!

[Anonymous]

Hi @johnt75 , 

I am not getting the answer which I was expecting. Please consider the below Table 1 as an input and the output which I am expecting using the mentioned formula is given on Table 2.

Table 1:

Actual or Forecast End	Total Benefits
01-01-22 0:00	3000
07-01-22 0:00	0
14-01-22 0:00	0
20-01-22 0:00	40000
13-02-22 0:00	8000
15-02-22 0:00	20000
16-02-22 0:00	0
18-02-22 0:00	50000
26-02-22 0:00	252000
28-02-22 0:00	332550

Table 2:

Actual or Forecast End	2022	2023	2024
01-01-22 0:00	£2750.00	£3000.00	£3000.00
07-01-22 0:00	£0.00	£0.00	£0.00
14-01-22 0:00	£0.00	£0.00	£0.00
20-01-22 0:00	£36666.67	£40000.00	£40000.00
13-02-22 0:00	£6666.67	£8000.00	£8000.00
15-02-22 0:00	£16666.67	£20000.00	£20000.00
16-02-22 0:00	£0.00	£0.00	£0.00
18-02-22 0:00	£41666.67	£50000.00	£50000.00
26-02-22 0:00	£210000.00	£252000.00	£252000.00
28-02-22 0:00	£277125.00	£332550.00	£332550.00

Please help me in solving this issue.

[johnt75]

Yearly Benefit =
var monthsDiff = DATEDIFF( 'Table'[Project Completion Date], DATE( SELECTEDVALUE( 'Date'Year' ), 12, 1), MONTH) 
var monthsBenefit  = SWITCH( TRUE(), 
   monthsDiff < 0, 0,
   monthsDiff > 12, 12,
   monthsDiff
)
return monthsBenefit * [Monthly benefit value]

where [Monthly benefit value] is a measure which calculates what the monthly benefit is, in your example 120 / 12.

[Anonymous]

But this is not giving my expected answer.

Consider the INPUT data as below, where for each Actual date we will have Total benefits.

Actual Date	Total Benefits
01-01-22	3000
15-02-22	20000
30-06-22	617000
16-01-23	220000
29-09-23	80000
01-05-24	40000
31-12-24	120000

And now, the output should be as the below table,

Actual Date	2022	2023	2024
01-01-22	£2750.00	£3000.00	£3000.00
15-02-22	£16666.67	£20000.00	£20000.00
30-06-22	£308500.00	£617000.00	£617000.00
16-01-23	0	£201666.67	£220000.00
29-09-23	0	£20000.00	£80000.00
01-05-24	0	£0.00	£23333.33
31-12-24	0	£0.00	£0.00

Calculation should be, For 1st data (i.e) 01-01-22 , Benefits starts in Feb 22 = 11 months of benefit in 2022. Formula is - (Total Benefit for that date /12) x (Remaining months of benefit)

                                          (i.e) - (3000/12) x 11= 2750  -> This is for Year 2022

For 2023, we have all 12 months of benefit in 2023. So it should be 3000. (i.e) - 3000/12 x 12

Please help me in achieving this approach using DAX. Thanks in advance!!

[johnt75]

Yearly Benefit = 
var monthsDiff = DATEDIFF( SELECTEDVALUE( 'Table'[Actual Date]), DATE( SELECTEDVALUE( 'Date'[Year] ), 12, 1), MONTH) 
var monthsBenefit = SWITCH( TRUE(), 
monthsDiff < 0, 0,
monthsDiff > 12, 12,
monthsDiff
)
return monthsBenefit * SELECTEDVALUE( 'Table'[Total Benefits] ) / 12

[Anonymous]

Can you attach me the solution PBIX file, please?

[johnt75 · Accepted solution]

PBIX file 

[Anonymous]

@amitchandak can you please help me on this asap?