topic Calculate the rent per month in Desktop

Calculate the rent per month

Anonymous — Wed, 09 Mar 2022 19:23:58 GMT

Hi guys I hope you are doing well

Lately I have been working on a small project and now i need to calculate the total sales of the company products

The company can sell or rent products to customers ,In my sales_table I have a column that indicates if the product is rented or been sold(FMD=0(SOLD),FMD=1(RENT)).

If the product been sold then i have no problem, however if the product is rented then the sales going to be AMOUNT/ 12 MONTH <the product is going to be rented for 1 year always> In other word let's suppose I have only 1 product rented in my sales table with these values DATE:01/01/2020,Amount=1200$

then my sales going to look something like this:

JANUARY 2020:100$

FEB 2020:100$

MARCH 2020:100$

DECEMBER 2020:100$

JANUARY 2021:0$

SINICE my data is so big i made a simple small example of my sales_table and I wil show you exactly what i Need as a result

1)First i m going to slice the date by month(choose a month of the year)

2)my matrix gonna show me the total sales of that month for each of the company centers(company centers are C1/C2.....)

Here is my sales_table

Slicing date with 2020 JANUARY:

RESULT expected:

Center	Total_sales
C1	100
C2	0

Slicing date with 2020 FEB:

RESULT expected:

Center	Total_sales
C1	100
C2	0

Slicing date with 2020 MARCH:

RESULT expected:

Center	Total_sales
C1	100
C2	100

Slicing date with 2020 APRIL:

RESULT expected:

Center	Total_sales
C1	100
C2	100

.....

Slicing date with 2021 FEB:

RESULT expected:

Center	Total_sales
C1	0(because the rent ended on 31-december-2020(1year))
C2	100

Slicing date with 2021 MARCH:

RESULT expected:

Center	Total_sales
C1	0
C2	0

I hope you guys understand what I need and I will be very thankfull for your help and thanks!

Re: Calculate the rent per month

JaromBIDEVatDK — Wed, 09 Mar 2022 17:07:51 GMT

I am a little confused by the way you provided the example but here is a stab at it.

Assuming that 1200/12 = 100 not 120 and that 1200 appears on each month for the 12 month period

Calculated column = IF(FMD = 1, Amount)/12, Amount)

Then you can just create a table using the new column!

Re: Calculate the rent per month

jdbuchanan71 — Wed, 09 Mar 2022 17:49:57 GMT

Anonymous

Assuming your monthly rent amount should be 100 and not 120 I think this measure will give you what you are looking for.

Rent Amount = VAR _Start = MIN ( Dates[Date] ) VAR _End = MAX ( Dates[Date] ) VAR _SalesWithMonths = FILTER ( ADDCOLUMNS ( ADDCOLUMNS ( Sales, "@EndDate", EOMONTH ( Sales[DATE], 12 ) - 1 ), "@Months", DATEDIFF ( MAX ( _Start, Sales[DATE] ), MIN ( _End, [@EndDate] ), MONTH ) + 1 ), Sales[FMD] = 1 && Sales[DATE] <= _End && [@EndDate] >= _Start ) RETURN SUMX( _SalesWithMonths, DIVIDE ( Sales[AMOUNT] , 12 ) * [@Months] )

In order for this to work my Dates table is NOT connected to the Sales table. I also wrote a measure for sales.

Sales Amount = VAR _Start = MIN ( Dates[Date] ) VAR _End = MAX ( Dates[Date] ) RETURN CALCULATE( SUM ( Sales[AMOUNT] ), Sales[FMD] = 0 && Sales[DATE] >= _Start && Sales[DATE] <= _End)

Then a totaling measure.

Total Amount = [Sales Amount] + [Rent Amount]

I have attached my sample file for you to look at.

Re: Calculate the rent per month

Anonymous — Wed, 09 Mar 2022 19:13:56 GMT

Oh my god you are amazing thanks for your help ,

yeah the amount per month is 100 sorry i didn't notice it ,it's not 120

but i have a question if I connect the date table with the sales table it will not work so in my project i have to remove that connection link?

Re: Calculate the rent per month

Anonymous — Wed, 09 Mar 2022 19:23:03 GMT

yeah the amount is 100 I have edited the post sorry for that and thanks for your help

Re: Calculate the rent per month

jdbuchanan71 — Wed, 09 Mar 2022 21:02:56 GMT

You do have to remove the connection or disable it in the measure. If it is linked up and you select say, June 2021, you would only see records from your sales table with a Date in June 2021 which is not what you want I don't think. You would not see any rent amounts from records with a Date in May 2021 for example.

Re: Calculate the rent per month

Anonymous — Thu, 10 Mar 2022 10:14:09 GMT

yeah that's exactly what happen then I will remove the link in the measure because i need the link for other type of calculation thanks for your time you helped me alot I have already considered your reply as solutions and thanks!