Dynamic view to inventory

[BBesler]

Hello,

I want to create a table or visual, where I can see the stored machines per day.

Therefore a have one table with 3 rows (Ordernbr, Booking date into the storage, Booking date out of the storage).
Every line is a mashine.

What I need is a table with the numbers of machines per day. 
I tried to calculate the values by using filters, what does not work.

Any idea, how to solve the problem?

Regards
Benno

[SamWiseOwl]

Hi @BBesler 

Create yourself a calendar table, there is a blog how to do it here: https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm

Join this onto your In booking column.

Put the day column in a visual and then create a measure:
Computers out =
var currentDay = selectedValue(calendartable[date])

Return
Calculate(
Count(table[Ordernbr]), Filter(AllSelected(table), [In Booking] <=currentDay && [Out Booking] >= currentDay))

[BBesler]

Hello,

the measure works, but I have still not the correct results.
I joined the new data table with the in booking column.
This effects that the result is related to the in bookong column. The x-axis is the date of the new date table.
So the result is related to the in booking date.

What I need is the sum of machines per day. 
If I use the in booking day, the result is not the total sum for this day, because there a mashines with a earlier in booking day, which are still on stock. 

The logic is correct. 
1. InBookingDate <= CurrentDate
AND
2. OutBooking Date >= CurrentDate

But If I use the InBookingDate the measure does not count all mashines which are in stock for the CurrentDay.

Any idea?

[SamWiseOwl · Accepted solution]

I've created a link to my download if you could check if this is working:Date Calculation.pbix

[BBesler]

Hello,

the solution is correct, but my figures show inncorrect results, because I have in my list also lines, without an outgoing booking. So the last days were wrong, because the Machines on stock does not respect the bank results.

This ist the measure for Row1

Machines on stock =

var currentDay = selectedValue(DimDateInventory[Date])

Var Calcnotblank =
Calculate(
Count('Machine bookings'[Material])
, Filter(
AllSelected('Machine bookings')
, currentDay >= [OnbookingDate] && currentDay<= [OffbookingDate]
)
)

Var Calcisblank =
Calculate(
Count('Machine bookings'[Material])
, Filter(
AllSelected('Machine bookings'[Material])
, currentDay >= [OnBookingDate] && [OffBookingDate] = Blank()
)
)

Return
if (
currentDay<= TODAY()
,Calcnotblank + Calcisblank
,BLANK()
)

Row2 = Calcisblank (w/ outbooking)
Row3 = Calcnotblank (w/o outbooking)

I created a measuer to identify the last date with an value for Calcisblank(Row2), which works.
Lastnotblank needs a table, so this does not work. 
How can I identify this last value and add it to the Total sum (Row1)