Dax calculate monthy and yearly rent payments with variables

[Gdv]

https://1drv.ms/u/s!AvCUiNWEUJeCq7Uj3wegyKJTjlsGMw?e=vusnte 

@lbendlin It took a long for me to shape it .. I also tried at my end to work on earlier solution provided by you and worked something which is also attached for your reference

Requirements

1. Rent Varies every no. of years ... R1 to R30, R1 is 1st renewal and corresponding increment is RE1 to RE30
2. Apart from Rent Licensee shares RevenueShareMonthly and Escalation in Revenue every year as RS I YoY%
3. Revenue from Car Parks allotted CarParks @ RatepCarPark
4. CAM Margin (Common Area Maintenance Charges) per sqft per month and Revenue from the same is Margin
5. IFSDM Amount deposited by the tenant to landlord at the time of start of lease and which landlord will return the same amount at the time of expirty of lease
6. AdministrativeCharges and EscalationRateA
7. Renting Management Expenses (RME) and EscalationRateRME
8. Renting Brokerage No. of Months at the Start of Rent
9. Renting Brokerage (Post Expiry) No. of Months of Rent, in case of Lease expires before termination date (EndDate)
10. Rent Calculation and Other Income to be Calculated for period  is from StartDate to EndDatehttps://1drv.ms/u/s!AvCUiNWEUJeCq7Uj3wegyKJTjlsGMw?e=anMqMA 

[v-yangliu-msft]

Hi  @Gdv ,

You can try checking R1-R30 - Transform - Unpivot Columns in Power Query.

Then create a calculated column showing the Index of the grouping, and use measure to implement the metrics for each month's totals.

Create calculated column：

Index =
var _len=LEN('2ProjectData'[Attribute])
var _find=FIND("R",'2ProjectData'[Attribute],1)
var _right=
VALUE(RIGHT([Attribute],_len-_find))
return
_right

Create Measure：

Measure =
SUMX(
    FILTER(ALL('2ProjectData'),
    '2ProjectData'[Licensee]=MAX('2ProjectData'[Licensee])&&'2ProjectData'[Index]<=MAX('2ProjectData'[Index])),[Value])

If the above results do not meet your expectations, can you express the expected results in the form of a picture, we can help you better

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.