Machine usage for each date [HELP!]

[mrhuguru]

Hey guys,

my company does photobooth rental and we have a huge logistic file with in it the following info (only the pertinent ones is this case) :

Booth Type / Number of booths / City / Install Date / Uninstall Date

I would like to calculate the number of photobooths used for each date (passed & to come), and be able to filter it per city & per type.

I have litteraly no idea how I could manage to do that, does anyone has an idea ?

Thank you so much,
Hugo

 

[akice]

Can you post a link to a sample file? and you say "...used for each date (passed & to come).." but I don't see a field for usage in your listing.

 

[mrhuguru]

Hey Akice, thank you for your answer.

Please find attached the sample file (I got rid of the irrelevant columns).

When I am talking about "usage", I mean that the machine is in use or will be in use. To determine if it is or not, I do not have a field but I need to calculate it using install & uninstall date.

This is why for each date of a calendar, I need to sum the machine quantity for each row if and only if said date is within "install date" & "uninstall date".

Not sure I make sense !

https://drive.google.com/file/d/0BytjTNsNJKZabVlkM1RWR1JKWjg/view?usp=sharing

 

[akice]

I created a Calendar table with all dates inclusive of the range in your sample data and added to data model. Took your FEUILLE PRINCIPALE sheet and added to data model. I did not create any relationships. Then created a pivot and added the Date values from my new Calendar table. Added this measure to pivot:

machines in use:=CALCULATE (    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        MAX ( Calendar[Date] ) >= Table1[INSTALL DATE]
            && MAX ( Calendar[Date] ) <= Table1[DATE DE DESINSTALLATION]
    )
)

You should be able to add slicers for city/state etc. if you add to table. However since the tables are not related you are going to get a warning and the slicers will look a bit weird (grayed out). But this should at least get you started.

 

[FDibbins]

Am not able/allowed to see your file, but you can probably do this with the COUNTIFS() function