topic Re: Library Books Checked Out on a Trend Line in DAX Commands and Tips

Library Books Checked Out on a Trend Line

davidataylor — Fri, 20 Mar 2020 01:31:36 GMT

I'm using Library Books as a proxy for a staff tracker that I built in Power BI for staff out of work related to COVID-19 (related to COVID-19, but not confirmed cases as we have many that are taking proper precautions and self-isolating), but I think I can ask my question a little easier using Library Books instead of staff. I have a tracker that tracks the book (one row per book) along with the check-out and check-in date, three (3) total columns. I'd like to create a running count of how many books were checked out at any given time that compares the check-out date with the check-in date with a date on a trendline and then sums the number that were checked out on the date on the trendline. For example, on 2/29, there was one book checked out and then returned on 3/18. So, for 3/9-18, this book would be counted. On 3/6, there was one book checked out until 3/13, so this one book would be counted for 3/6-13. On 3/9, there were 19 books checked out. Thus, on 3/9, there was a total of 21 books overall checked out. On 3/10, seven (7) of those books were returned with four (4) additional checked out. Thus, on 3/10, there were 21-7+4=18 books checked out. This pattern continues with books being checked-out and checked-in daily and I need to be able to count how many are out at any given time and chart that on a daily trendline graph.

How do I create a DAX formula in my Power BI dashboard that shows the number of books checked out on trendline chart showing the number of books being used on that day?

Thanks ahead of time!

Re: Library Books Checked Out on a Trend Line

Greg_Deckler — Fri, 20 Mar 2020 04:03:26 GMT

Difficult to say. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Maybe Open Tickets? https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Re: Library Books Checked Out on a Trend Line

davidataylor — Fri, 20 Mar 2020 13:05:06 GMT

Ok, thanks for your reply. I suppose it's good to know that while I struggled half of yesterday trying to figure this out, it was a difficult for a super-user too! I'll check out the links you provided.

Re: Library Books Checked Out on a Trend Line

Anonymous — Fri, 20 Mar 2020 23:46:10 GMT

One sec and you'll have the solution.

Best
D

Re: Library Books Checked Out on a Trend Line

Anonymous — Sat, 21 Mar 2020 00:17:21 GMT

Here's your solution:

https://1drv.ms/u/s!ApyQEauTSLtOgYM3-3PJaXyYrnL4YQ?e=7AxcKp

Best
D

Re: Library Books Checked Out on a Trend Line

Anonymous — Sat, 21 Mar 2020 00:19:02 GMT

Ah... Bear in mind that when CheckIn in the table in the file is blank it means the book has not yet been returned.

Best
D