Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Qty | Startdate | Stopdate | |
ITEM1 | 3 | 2017-01-03 | 2017-01-12 |
ITEM1 | 4 | 2017-01-06 | 2017-01-07 |
ITEM1 | 2 | 2017-01-07 | 2017-01-15 |
ITEM2 | 6 | 2017-01-03 | 2017-01-12 |
ITEM2 | 2 | 2017-01-07 | 2017-01-15 |
Desired output:
2017-01-01 | 2017-01-02 | 2017-01-03 | 2017-01-04 | 2017-01-05 | 2017-01-06 | 2017-01-07 | 2017-01-08 | 2017-01-09 | 2017-01-10 | 2017-01-11 | 2017-01-12 | 2017-01-13 | 2017-01-14 | 2017-01-15 | |
ITEM1 | 0 | 0 | 3 | 3 | 3 | 8 | 9 | 5 | 5 | 5 | 5 | 2 | 2 | 2 | 2 |
ITEM2 | 0 | 0 | 6 | 6 | 6 | 6 | 8 | 8 | 8 | 8 | 8 | 8 | 2 | 2 | 2 |
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.
Solved! Go to Solution.
You need to generate a date list - luckily this is very easily done in the Query Editor @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
That should do it!
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.
You need to generate a date list - luckily this is very easily done in the Query Editor @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
That should do it!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |