Dax calculate monthy and yearly rent payments with variables

Gdv — Fri, 30 Aug 2024 07:30:05 GMT

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

Rent Varies every no. of years ... R1 to R30, R1 is 1st renewal and corresponding increment is RE1 to RE30
Apart from Rent Licensee shares RevenueShareMonthly and Escalation in Revenue every year as RS I YoY%
Revenue from Car Parks allotted CarParks @ RatepCarPark
CAM Margin (Common Area Maintenance Charges) per sqft per month and Revenue from the same is Margin
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
AdministrativeCharges and EscalationRateA
Renting Management Expenses (RME) and EscalationRateRME
Renting Brokerage No. of Months at the Start of Rent
Renting Brokerage (Post Expiry) No. of Months of Rent, in case of Lease expires before termination date (EndDate)
Rent Calculation and Other Income to be Calculated for period is from StartDate to EndDatehttps://1drv.ms/u/s!AvCUiNWEUJeCq7Uj3wegyKJTjlsGMw?e=anMqMA

Re: Dax calculate monthy and yearly rent payments with variables

Anonymous — Mon, 02 Sep 2024 02:20:49 GMT

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.

topic Dax calculate monthy and yearly rent payments with variables in Developer

Dax calculate monthy and yearly rent payments with variables

Re: Dax calculate monthy and yearly rent payments with variables