Counting number of times an action happens within a date/time range

[Anonymous]

Good day,

I am new to Power BI, and have what I assume to be a simple problem, but I have been unable to solve for it.  Looking at a chain of fish stores, I would like to get a count of the number of times a freezer was opened at each store within 45 minutes of the store receiving a delivery.  I have three tables: deliveries, freezer openings, and stores.  Deliveries, and freezer openings are linked to stores using a many to 1 relationship based on store number.

My current view has simply the store and delivery time and date.

I would like for the end state to look something like this (pardon my ms paint mockup)

There will be plenty of additional freezer openings outside of the 45 minute window, but these do not concern me.  I am just interested in tracking the ones following a delivery.

I have tried using datesbetween, but it does not seem to work for my purposes.

PBIX and excel workbook I used to get the data can be downloaded from the link below.  Please let me know if there are any further details I can provide.

https://1drv.ms/u/s!AsjSzB7eFP17okg9gSQnWmA8R5hY?e=z0Sx6d

[NickHoffmans]

I have a problem that is (I think) quite similar to the problem above. However, I've not been able to solve it myself. Hope anyone can help.

We have machines for rent. The machines are separated by groups, which contain article numbers. These articles have a status to indicate whether they are available or not.  Reservations are made on group-level, on the day before it is decided which specific article will be delivered. I would like to count the number of reservations made per group somewhere in the next 7 days (moving timeframe).

The machine inventory table is related to the articles in the contract(s).

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[NickHoffmans]

Hi Ashish,

Please find a small dummy here: https://burgersverhuur-my.sharepoint.com/:f:/g/personal/nick_hoffmans_burgersverhuur_nl/EpbsWG9hEmNG.... First time I'm uploading here, hope this works. The access will expire at September 10th. 

I would now would like to retrieve the following information:

Reservations:

Group 53022 = 2 (the reservation for 25-09-2022 is not counted, since I only want the ones included that are reserved for next 7 days)

Group 53028 = 1

Having this, I can calculate the net availability per group, in this case the net availability for group 53022 would become -1 ==> Total inventory of 3, of which are:

• 1 on hire,
• 1 in repair,
• 1 available,
• 2 reservations
  3 - 1 -1 - 2 = -1

[Ashish_Mathur]

Hi,

I cannot understand the following:

1. Reservations of 53022 should be 3 (not 2) because 7 days from today's date (Sept 6) is Sept 13.
2. How is the total inventory 3 - is it because there are 3 rows in the inventory sheet?
3. If my assumption in point 1 is correct, then remaiing inventory should be 3-3 = 0?  Am i correct?
4. Why is there no date of inventory column in the Inventory table?

[NickHoffmans]

Hi Ashish,

1. No, reservations should be 2. 053022-002 (contract 1) is already on hire (which is also displayed in the inventory where machine 53022 has status "On Hire"), so no reservation anymore. The remainder (to be deliverd on 8th and 9th September) are reservations. 

2. Yes, that is correct. There are 3 machines of type 053022

3. See the explanation in point 1

4. What do you mean with date of inventory? The inventory is always "live".

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.