Aug 15 2021 09:24 PM
Aug 15 2021 09:24 PM
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!
Aug 16 2021 02:48 PMSolution
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] <= endDate, USERELATIONSHIP ( 'Calendar'[Date], Table1[Date_Out] ) )
Qty In:=VAR endDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( Table1[Qty] ), ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < endDate, USERELATIONSHIP ( 'Calendar'[Date], Table1[Date_In] ) )
Qty Diff:=[Qty Out] - [Qty In]
gives Qty Out for each date (more exactly for the end of each period)
Max in Period:=MAXX ( VALUES ( 'Calendar'[Date] ), [Qty Diff] )
gives max Qty Out in the period.
PivotTable could be as
Please check in attached.