Defining Dates between Opening and Closing date (book rental)

[didijaba]

HI, I have source file with records of book rental, and each row has has general data, including Opening date (date/time) when book was rented, and Closing date (date/time) when book was returned. I would like to see how many books are out on some day, or on some period of time using slicers for filtering book categorie , month, week, ... (in the end). I do not know how to define this, pls advice me.Thanks in advance.

 

[Matt Allington]

There are a few ways to do this. The trick is that you need a formula that filters the data table >= the start date and <= the end date. One way is to have 2 calendar tables (as in my demo)

https://www.dropbox.com/s/kpfom7bwsqwvkit/book lending.xlsx?dl=0

Another way is to use 2 relationships to the one calendar table. One will be inactive and the DAX will be different. You can read about multiple relationships in my Knowledge Base here Multiple Relationships Between Tables in DAX - Excelerator BI

One advantage of multiple relationships is you can use a timeline slicer - which is quite neat.

Also note my formula counts book lending events, not the number of books lent. Ie the same book could be lent 3 times in the time period selected. My formula counts that book 3 times.

Count of Books Out:=CALCULATE(COUNTROWS(Data),       FILTER(all(OpenCal),
           OpenCal[Open Date] >= MIN(OpenCal[Open Date])
       ),
      filter(all(CloseCal),
           CloseCal[Close Date] <= max(CloseCal[Close Date])
      )
)

To count each book once, you could use something like this

Count of Unique Books Out:=CALCULATE(DISTINCTCOUNT(Data[Book]),       FILTER(all(OpenCal),
           OpenCal[Open Date] >= MIN(OpenCal[Open Date])
       ),
      filter(all(CloseCal),
           CloseCal[Close Date] <= max(CloseCal[Close Date])
      )
)

 

[didijaba]

Thanks, you have helped me a lot . Have a good day, and thanks again

 

[didijaba]

Just one more question, as you probably solved it already somewhere. What if I have some time limit on returning books, for example, I reserve book for 5 days, and return it after 7 days,2 hours, 10 minutes, (and you can be tolerated for 2 hours), but if over agreed reserved 5 days and 2 hours tolerance now you pay penalty for 3 days (7-5=2 + one more day for going over 2 hour tolerance). How could that be set? Thanks in advance, I know this is not easy, I have been banging my head over it for some time now .

 

[Matt Allington]

I guess it depends if there is a standard 5 days or if it varies by book. But you could write a simple measure like this

days out:=sumx(data,data[close date] -data[open date])

then an you could just write
days late:=if([days out] > 5,[days out]-5)

 

[didijaba]

you helped me, thanks

 

[jaschro]

(After writing this, I realized that this is the PowerBI forum, not the Excel forum. The below is an Excel answer).


This is a general problem disguised as an industry specific question. You have a bunch of events. Each event has a start date and it may or may not have an end date. You need to know the balance at any given point in time. A simple way to approach this is to recognize that the ending balance for each period is the beginning balance + additions - returns. If the dates the books are checked out and returned are named "checkoutdates" and "returndates", respectively, then you could write something like this:

So, if you have the beginning balance, then you can compute the number of additions with a =COUNTIFS function.

=COUNTIFS(checkoutdates,">"&1/1/15,checkoutdates,"<="&1/31/15)

And the number of returns is also a =COUNTIFS function.
=COUNTIFS(returndates,">"&1/1/15,returndates,"<="&1/31/15)

And then you just need the beginning balance, which you can get by making the first date =min(checkoutdates)

 

[didijaba]

what is bothering me righ now, how to solve case if I have rule that if book returning is late for more then 2 hours, you count aditional day. So for example if person returns book after 3 days and 1 hour, I count that book as three days out, but if person returns book after 3 days and 3 hours, I count that book as four days out. Same applies for any other combination of day numbers (from 1-1000). And I want to see books out (including penalty added book) to see if renting books per day for few cents is better then doing monthly fee.