Calculate OPEX distributed over a period of 15 Years for multiple data entries using DAX function

[Kavya_Mahesh]

Hi,

I am looking to calculate OPEX using DAX functions. 

Please find below an example of what I am trying to achieve.

Let say I have Total OPEX column from 2020 to 2025. I want to calculate the OPEX distribution for each year over 10 years ie 2035. And when I create a total Opex for 2020, 2021, 2022, up until 2035, the result should be the one highlighted with yellow.

I would really appreciate if I can get help on this.

Regards

Mahesh

[v-sdhruv]

Hi @Kavya_Mahesh 
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank you

[v-sdhruv]

Hi @Kavya_Mahesh 
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank you

[v-sdhruv]

Hi @Kavya_Mahesh ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Please feel free to contact us if you have any further questions.
Thank you

[danextian]

Hi @Kavya_Mahesh 

Your description is confusing. You want to spread OPEX over 10 years but from 2020 to 2030,  that would be 11 and so the total would be 110% of the opex value. That aside, you wil need to create a disconnected table containing a column of years to spread over and then measures referencing it.

Years = GENERATESERIES(2020, 2035, 1)

Opex Spread = 
VAR _spread =
    DIVIDE ( SUM ( 'Table'[Opex Value] ), 10 )
VAR _start =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _end = _start + 10
RETURN
    IF (
        SELECTEDVALUE ( Years[Years] ) >= _start
            && SELECTEDVALUE ( Years[Years] ) <= _end,
        _spread
    )

Opex Spread Total = 
SUMX (
    SUMMARIZECOLUMNS ( 'Table'[Year], 'YearsTable'[Years], "@value", [Opex Spread] ),
    [@value]
)

Please see the attached sample pbix.

[Ashish_Mathur]

Hi,

Share some raw data to work with.  Share data in a format that can be pasted in an MS Excel file.