topic Re: How to get the Annual benefit value for the left over months? in DAX Commands and Tips

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

Anonymous — Fri, 11 Mar 2022 09:03:45 GMT

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 !!

Re: How to get the Annual benefit value for the left over months?

CNENFRNL — Fri, 11 Mar 2022 11:05:05 GMT

Tricky solution,

Re: How to get the Annual benefit value for the left over months?

Anonymous — Fri, 11 Mar 2022 12:45:53 GMT

Hi CNENFRNL ,

But where is the Total Benefits data? For every year, we will have a total benefit value. In an example which I have explained above has a Total Benefit of 120, from which we are calculating the Annual Benefits values.

Can you please help me with the DAX, by applying Total Benefits data as well

Re: How to get the Annual benefit value for the left over months?

CNENFRNL — Fri, 11 Mar 2022 12:55:19 GMT

Re: How to get the Annual benefit value for the left over months?

Anonymous — Sat, 12 Mar 2022 07:37:02 GMT

But this is not 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!!