Subrental Supply Analysis to Purchase

Question

Hi everyone, I am trying to automate and create meaningful analyses of Subrental PO data for my company. We are a Film Production Equipment Rental Company that often has to subrent equipment from other Companies to fulfill the needs of our clients, and to figure out how much of each rental equipment product we would have needed to purchase to increase our inventory supply to meet those demands I started creating this spreadsheet.

However I am only able to compare Rental Products one at a time in this spreadsheet, whereas I'd like to be able to convert the MAX values of all the Products into a pivot table broken up by monthly MAXs to see trends.

The basic way it works is I have a date sequence above the table for 31 days as an example (ideally I could have data for a longer range than this) and I use a SUMIF formula referencing each date column to count the quantities of subrentals for each day of a certain subrental product. Then I find the MAX of that set for the month. But this only works right now for a certain specified Rental Product, and I'd like to find a better way to automate this.

I've attached the spreadsheet and I'd love to hear your thoughts or ideas on automating and making this spreadsheet more efficient!

sergeibaklan · Accepted Answer

mkloker&nbsp;
It could be done with PivotTable using data model.
- creating PivotTable add your data to data model;
- in Power Pivot create Date table
(above is straightforward way, in general above better to do with Power Query)
- create relationships on data for above two tables

- add DAX measures
Qty Out:=VAR endDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        ALL ( 'Calendar'[Date] ),
        Table1[Date_Out] &lt;= endDate,
        USERELATIONSHIP ( 'Calendar'[Date], Table1[Date_Out] )
    )
&nbsp;and
Qty In:=VAR endDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] &lt; endDate,
        USERELATIONSHIP ( 'Calendar'[Date], Table1[Date_In] )
    )
with them
Qty Diff:=[Qty Out] - [Qty In]
&nbsp;gives Qty Out for each date (more exactly for the end of each period)
Finally
Max in Period:=MAXX (
    VALUES ( 'Calendar'[Date] ),
    [Qty Diff]
)
gives max Qty Out in the period.
PivotTable could be as

Please check in attached.

Forum Discussion

Subrental Supply Analysis to Purchase

1 Reply