Hi,
I am new to Power BI and have been struggling with this issue for days, any help would be appreciated
I have packing list information which I want to sort out into weekly buckets so I can model forward inventory positions. Each packing list can have the same item on it multiple times and I can have multiple shipments, all with different available dates. What I have tried works fine as long as I don't have more than 1 shipment with the same item on it, if that is the case I either get the total quantity on the water all in week 1, instead of over the multiple weeks, no shipment information at all or multiple lines based on teh available dates
I have a measure which calculates how many days from today is the shipment Available Date
DaysDif = DATEDIFF(TODAY(),SELECTEDVALUE(PackList3[Avail Date]),DAY)
and have tried the following measures with SUM(), SUMX() and also tried using CALCULATE() with no joy
PO 07 = IF([DaysDif]<=7,SUMX(PackList3,PackList3[PKL_Qty]),BLANK())
PO 14 = IF(AND([DaysDif]>7,[DaysDif]<=14),SUMX(PackList3,PackList3[PKL_Qty]),BLANK())
PO 21 = IF(AND([DaysDif]>14,[DaysDif]<=21),SUMX(PackList3,PackList3[PKL_Qty]),BLANK())
etc
Thanks
Don
Hi All, I have solved the problem
In query editor, I added a new custom column 'DaysDif" and calculated the days differnce between the available date and today using
Duration.Days([Avail Date]-DateTime.FixedLocalNow())
I have then used CALCULATE()
PO 07 = CALCULATE(SUM(PackList3[PKL_Qty]),PackList3[DaysDif]<=7)
PO 14 = CALCULATE(SUM(PackList3[PKL_Qty]),AND(PackList3[DaysDif]>7,PackList3[DaysDif]<=14))
PO 21 = CALCULATE(SUM(PackList3[PKL_Qty]),AND(PackList3[DaysDif]>14,PackList3[DaysDif]<=21))
etc to put the arival quantites into weekly buckets
for anyone else that wants to do something like this...
I then calculated the Stock On Hand at the end of the week
SOH 07 = [StockOnHand]+ [PO 07] -([Ave Wkly FC])
etc for SOH 14, SOH 21
added another measure (not displayed) to figure out the number of weeks cover the SOH represented
WSOH 07 = DIVIDE(DailySOH[SOH 07],[Ave Wkly FC],0)
etc for the rest of the weeks
then I added another yet another measure to work out what colour I wanted the SOH cells to be (
ColourItem 07 =
IF([WSOH 07]<=0,[ColRed],
IF([WSOH 07]<[AtRisk],[ColOrange],[ColWhite]))
colours were defined eg ColRed = "#FD5959"
now I have a colour coded report, where red = OOS and orange is atrisk of stock out (<3 weeks SOH)
the first row below is curretly OOS but when tomorrows container gets booked in, the item inventory will be fine
The second row ditto
3rd row, I have massive troubles with, currenly OOS and nothing on the water, this means I have to air freight stock ASAP
7th row, I have 1500 in this weeks container, but in 3 weeks I will be at risk of stock out, so I had better have some stock in the next shipment 🙂
