Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hakalf
New Member

Number of items used based on in - out date

Hello

New user here.

 

I have a booking type of table with start & stop dates and the number out on these dates. I want to calculate the number out per specific date in a table. 

 

Indata:

 QtyStartdateStopdate
ITEM132017-01-032017-01-12
ITEM142017-01-062017-01-07
ITEM122017-01-072017-01-15
ITEM262017-01-032017-01-12
ITEM222017-01-072017-01-15

 

Desired output:

 

 2017-01-012017-01-022017-01-032017-01-042017-01-052017-01-062017-01-072017-01-082017-01-092017-01-102017-01-112017-01-122017-01-132017-01-142017-01-15
ITEM1003338955552222
ITEM2006666888888222

 

You might ask, why not the dates in vertical? Well we have like 4000 Types of items that we need to keep track of utilization rates individually.

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@hakalf

You need to generate a date list - luckily this is very easily done in the Query Editor Smiley Wink @MarcelBeug

1) Select both Date columns

2) Change their Data Type from Date to Whole Number

3) Add a Custom Column as shown in the picture {[Startdate]..[Stopdate]}

4) Expand to New Rows

5) Select all 3 Date Columns and Change their the Data Type to Date

6) Create the Matrix

Query Editor - 2 - Generate - Date List2.gif

That should do it! Smiley Happy

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

You can use matrix visual to achieve this.

 

- put item field on rows

- put start date field on columns

- put qty on value

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you for the tip, but it´s not that simple. There is a stopdate to consider as well.

 

The thing is that Items go out on startdate, then they comes in on stopdate, and therefore the balance is shifting day per day and you have to sum per day if a quantity of a particaluar item only if the booking row falls between the startdate och stopdate.

 

Sean
Community Champion
Community Champion

@hakalf

You need to generate a date list - luckily this is very easily done in the Query Editor Smiley Wink @MarcelBeug

1) Select both Date columns

2) Change their Data Type from Date to Whole Number

3) Add a Custom Column as shown in the picture {[Startdate]..[Stopdate]}

4) Expand to New Rows

5) Select all 3 Date Columns and Change their the Data Type to Date

6) Create the Matrix

Query Editor - 2 - Generate - Date List2.gif

That should do it! Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.