Calculate rental value using historical invoicing hourly rates

[LadyPeshet]

Hello,

I have two tables. Rentals and Invoicing.

Example:

Rentals (provided monthly)

ID  DATE_EOM    HOURS_USED
1   07/31/2016  20
1   08/31/2017  40
1   06/30/2018  133
2   08/31/2017  97
2   08/31/2016  21
3   12/31/2015  37

Example:

Invoicing

ID  START_DATE  END_DATE      INV_RATE
1   06/24/2016  06/01/2017    10.22
1   06/02/2017  06/23/2018    11.54
1   06/24/2018                13.60
2   08/06/2017                15.70
2   08/08/2016  08/05/2017    12.62
3   12/12/2015                12.00

Blank values in End_Date are representing latest invoicing rates.

The calendar table exists and has relationships established with all dates but they are inactive in the model. 

I would like to calculate the sales amount taking into account changes in the invoicing rate during a calendar year.

How it can be calculated?

[v-xinruzhu-msft]

Hi @LadyPeshet 

You can create a new column in invoicing:

Column = var a=CALCULATE(SUM(Rentals[HOURS_USED]),FILTER('Table',[Date]>=EARLIER(Invoicing[START_DATE])&&[Date]<=IF(EARLIER(Invoicing[END_DATE])=BLANK(),MAX('Table'[Date]),EARLIER(Invoicing[END_DATE]))),FILTER(Rentals,Rentals[ID]=EARLIER(Invoicing[ID])))
return SUMX(FILTER(Invoicing,Invoicing[ID]=EARLIER(Invoicing[ID])&&[START_DATE]<=EARLIER(Invoicing[START_DATE])),a*[Invoicing])

Best Regards!

Yolo Zhu

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

[LadyPeshet]

Hi @v-xinruzhu-msft ,

Thank you for your support on this! The calculated column approach works for me but I've noticed that time intelligence coming from Date table is not possible when using this logic.

Date Table used as Slicer - no change in newly created column

Out of this rental value I also need to be able to create YTD, MTD, 12 rolling month, etc. and use Date table as a slicer. Is there any way to obtain this?

Thank you once again and best regards!

[v-xinruzhu-msft]

Hi @LadyPeshet 

What is the relationship between date table and invoicing table, can you provide a sample of relationship of three tables?

Best Regards!

Yolo Zhu

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

[LadyPeshet]

Hi @v-xinruzhu-msft ,

The current relationship is following:

Invoicing Table is connected to Date table with START_DATE and END_DATE (one to many relationships) but these relationships are currently inactive. 'Rentals'[DATEEOM] is the active one. Both tables should be connected to Rental Items (bridge table).

BR!

[Anonymous]

This may work...

1. In the Rentals table, create a measure to calculate the total hours used for each year.

Total Hours Used =
SUM(Rentals[HOURS_USED])

2. In the Invoicing table, create a measure to calculate the invoicing rate for each year.

Invoicing Rate =
CALCULATE(
    MAX(Invoicing[INV_RATE]),
    DATESBETWEEN(
        Calendar[Date],
        MIN(Invoicing[START_DATE]),
        ISBLANK(Invoicing[END_DATE])
            ? MAX(Calendar[Date])
            : Invoicing[END_DATE]
    )
)

3. Create a measure in the Rentals table to calculate the sales amount for each year.

Sales Amount =
[Total Hours Used] * [Invoicing Rate]

[LadyPeshet]

Hi @Anonymous ,

Unfortunately, this solution is not working.

Thank you for your input and best regards!