Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
khemiri
Frequent Visitor

Calculate the rent per month

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

sales.PNG

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!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@khemiri 

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]

jdbuchanan71_0-1646848151872.png

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

 

 

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User
Super User

@khemiri 

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]

jdbuchanan71_0-1646848151872.png

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

 

 

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? 

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.

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
Helper II
Helper II

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!

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors