Forum Discussion

BBesler's avatar
BBesler
Regular Visitor
2 years ago
Solved

Dynamic view to inventory

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 ...
Need Help
SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

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's avatar
BBesler
Regular Visitor
2 years ago

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's avatar
    SamWiseOwl
    Super User
    2 years ago

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

    • BBesler's avatar
      BBesler
      Regular Visitor
      2 years ago

      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)