cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Aggregating quantities into multiple columns by date

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 🙂
 

PSIR.PNG

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Aggregating quantities into multiple columns by date

Hi @DonW ,

 

could you provide a sample PBIX File?

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Highlighted
Community Support
Community Support

Re: Aggregating quantities into multiple columns by date

@DonW ,

 

Could you please share some sample data and give the expected result?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors