Calculate the rent per month

[Anonymous]

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)

<I already have a date table that contains dates starting from 01/01/2020 to 31/12/2022>

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!

[jdbuchanan71 · Accepted solution]

@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.

[Anonymous]

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? 

[jdbuchanan71]

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.

[Anonymous]

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!

[JaromBIDEVatDK]

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!

[Anonymous]

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