Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm pretty new to PowerBI and DAX and want to create a report for monitoring our available stock over time.
I’m working for a clothing company so we are selling our products over different seasons/collections.
The user will first select the correct collection and he will then get a list of all the products (ModelNo + ColourNo) that he can choose from. He then chooses for which warehouse (slicer) the available stock should be calculated.
The registers I will use are:
collection
[collectionCode]
[modelNo]
[colourNo]
[modelColour]
collectionCode | modelNo | colourNo | modelColour |
SS18 | 2020 | 600 | 2020600 |
SS18 | 2020 | 700 | 2020600 |
AW18 | 3460 | 100 | 3460100 |
article
[modelColour]
[size]
articleNo | description | modelColour | size |
20206001 | Testarticle 1 600-black S | 2020600 | S |
20206002 | Testarticle 1 600-black M | 2020600 | M |
20206003 | Testarticle 1 600-black L | 2020600 | L |
20206004 | Testarticle 1 600-black XL | 2020600 | XL |
20206005 | Testarticle 1 600-black XXL | 2020600 | XXL |
20206006 | Testarticle 1 600-black XXXL | 2020600 | XXXL |
20207001 | Testarticle 1 700-green S | 2020700 | S |
20207002 | Testarticle 1 700-green M | 2020700 | M |
20207003 | Testarticle 1 700-green L | 2020700 | L |
20207004 | Testarticle 1 700-green XL | 2020700 | XL |
20207005 | Testarticle 1 700-green XXL | 2020700 | XXL |
20207006 | Testarticle 1 700-green XXXL | 2020700 | S |
346010034 | Testarticle 2 100-white 34 | 3460100 | 34 |
346010036 | Testarticle 2 100-white 36 | 3460100 | 36 |
346010038 | Testarticle 2 100-white 38 | 3460100 | 38 |
346010040 | Testarticle 2 100-white 40 | 3460100 | 40 |
346010042 | Testarticle 2 100-white 42 | 3460100 | 42 |
346010044 | Testarticle 2 100-white 44 | 3460100 | 44 |
346010046 | Testarticle 2 100-white 46 | 3460100 | 46 |
stock
[modelColour]
[articleNo]
[warehouseCode]
[stockQty]
articleNo | description | warehouseCode | qty |
20206001 | Testarticle 1 600-black S | 02 | 0 |
20206002 | Testarticle 1 600-black M | 02 | 0 |
20206003 | Testarticle 1 600-black L | 02 | 2 |
20206004 | Testarticle 1 600-black XL | 02 | 4 |
20206005 | Testarticle 1 600-black XXL | 02 | 2 |
20206006 | Testarticle 1 600-black XXXL | 02 | 1 |
20206001 | Testarticle 1 600-black S | 04 | 5 |
20206002 | Testarticle 1 600-black M | 04 | 15 |
20206003 | Testarticle 1 600-black L | 04 | 25 |
20206004 | Testarticle 1 600-black XL | 04 | 25 |
20206005 | Testarticle 1 600-black XXL | 04 | 11 |
20206006 | Testarticle 1 600-black XXXL | 04 | 3 |
20207001 | Testarticle 1 700-green S | 02 | 0 |
20207002 | Testarticle 1 700-green M | 02 | 0 |
20207003 | Testarticle 1 700-green L | 02 | 0 |
20207004 | Testarticle 1 700-green XL | 02 | 0 |
20207005 | Testarticle 1 700-green XXL | 02 | 0 |
20207006 | Testarticle 1 700-green XXXL | 02 | 0 |
346010034 | Testarticle 2 100-white 34 | 02 | 25 |
346010036 | Testarticle 2 100-white 36 | 02 | 55 |
346010038 | Testarticle 2 100-white 38 | 02 | 60 |
346010040 | Testarticle 2 100-white 40 | 02 | 75 |
346010042 | Testarticle 2 100-white 42 | 02 | 40 |
346010044 | Testarticle 2 100-white 44 | 02 | 28 |
346010046 | Testarticle 2 100-white 46 | 02 | 3 |
warehouse
[warehouseCode]
[warehouseName]
warehouseCode | warehouseName |
02 | Warehouse A |
04 | Warehouse B |
allocation
[articleNo]
[warehouseCode]
[period]
[date]
[type] (Purchase = P and Order = O)
[qty]
articleNo | warehouseCode | period | periodStartDate | type (Purchase or Order) | qty |
20206001 | 02 | 1 | 2017-10-27 | O | -1 |
20206001 | 02 | 1 | 2017-10-27 | O | -3 |
20206001 | 02 | 1 | 2017-10-27 | O | -1 |
20206001 | 02 | 1 | 2017-10-27 | O | -1 |
20206001 | 02 | 1 | 2017-10-27 | O | -1 |
20206001 | 02 | 2 | 2018-02-01 | P | 20 |
20206001 | 02 | 2 | 2018-02-01 | O | -2 |
20206001 | 02 | 2 | 2018-02-01 | O | -3 |
20206002 | 02 | 2 | 2018-02-01 | P | 50 |
20206002 | 02 | 2 | 2018-02-01 | O | -2 |
20206002 | 02 | 2 | 2018-02-01 | O | -3 |
20206002 | 02 | 2 | 2018-02-01 | O | -3 |
20206002 | 04 | 1 | 2017-10-27 | O | -1 |
20206002 | 04 | 1 | 2017-10-27 | O | -1 |
20206002 | 04 | 1 | 2017-10-27 | O | -1 |
I want to create a matrix visual as below with PeriodStartDate as rows and sizes as columns.
| Sizes | |||||
PeriodStartDate | S | M | L | XL | XXL | XXXL |
2017-10-27 | 0 | 2 | 2 | 1 | 0 | 0 |
2018-02-01 | 10 | 25 | 35 | 33 | 17 | 11 |
2018-04-01 | 50 | 111 | 130 | 119 | 70 | 49 |
The first period will always be todays date (period = 1).
Each new purchase from our suppliers will create new periods (2, 3 and so on) and the startDate for that period will be the date (ETA) when the shipment arrives to our warehouse.
The order rows are then allocated to these periods depending on different conditions.
My problem is to calculate the correct quantity for each period and size for a chosen modelColour.
The calculations have to be filtered on modelColour, warehouseCode, sizes and periodStartDates.
If there for example aren’t any order reservations in period 1 (articleNo 20206002) I won’t get any periodStartDate for period 1 from the allocation register.
I would need to do calculations to determine the different periodStartDates (filtered on modelColour, warehouseCode and size) and then calculate the correct available stock for each size and period.
To calculate the available stock it would be done as below:
Period 1: stock[stockQty] (filtered on modelColour, warehouseCode and size) + allocation[qty] (filtered on modelColour, warehouseCode, size and allocation[period] = 1)
Period 2: stock[stockQty] (filtered on modelColour, warehouseCode and size) + allocation[qty] (filtered on modelColour, warehouseCode, size and allocation[period] <= 2)
Period 3: stock[stockQty] (filtered on modelColour, warehouseCode and size) + allocation[qty] (filtered on modelColour, warehouseCode, size and allocation[period] <= 3)
and so on.
Could someone please explain to me how to translate this scenario into DAX formulas?
Thanks!
@Guuuuschi,
Could you please verify the following points?
1. What fields do you use to create relationship among the above tables?
3. How do you get 2018-04-01 PeriodStartDate in the Matrix visual? I don't see this value in your allocation table.
3. How do you return the numerical values in the Matrix visual based on the above sample data?
Regards,
Lydia
Hi Lydia,
The user will select a collectionCode from the collection register in a slicer.
The collection register will now be filtered to only show modelColour for the chosen collection in another slicer.
The user now chooses one of the modelColour available.
The collection register will have a relationship with the article register through the field collection[modelColour] and articles[modelColour]. I need this register to be able to group on sizes (articles[size]) and not only model and colour in my matrix visualization.
The article register will have relationsships that looks like below:
articles[articleNo] -> stock[articleNo]
I need this to be able to include the stock quantities in my calculation
articles[articleNo] -> allocation[articleNo]
I need this to be able to include order reservations and new purchases that are coming home for each available period.
Finally we have the warehouse register that will have the following relationsships:
warehouse[warehouseCode] -> stock[warehouseCode]
warehouse[warehouseCode] -> allocation[warehouseCode]
This will be used in a slicer to filter for which warehouse the calculation should be done.
I can now filter the stock and allocation register down on the correct article numbers (modelColour down on sizes) and warehouse.
To determine the different periods and its startdates I would need to do a calculation that finds all the rows in the allocation register for the field [type] that has a value of P. These rows are all the incoming purchases and will determine how many periods that exists and which startDate they have. The first period will always be "Today". If there aren't any rows with P there will only be one period that starts Today. If there are rows with P the row with the lowest periodStartDate will be period 2, the row with P with the second lowest periodStartDate will be Period 3 and so on.
This is were my problem starts as I don't know how to calculate this. It would need to be some kind of table as I need the periodStartDates as a grouping in my matrix visual for each size.
Then we need to calculate the available stock for each size for those determined periods.
Period 1 (periodStartDate = Today) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] = 1). Order reservations will have negative quantities so if we have 20 pcs on stock and 10 pcs on reservations the result should be 20+(-10) = 10 pcs.
Period 2 (periodStartDate = the date for the row where allocation[type] is P and has the lowest [periodStartDate], for example 2018-02-01) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] <=2).
For example we purchased 50 pcs and have 20 pcs on order in that period. The result would be 20+(-10+50-20) = 40 pcs
Period 3 (periodStartDate = the date for the row where allocation[type] is P and has the second lowest [periodStartDate], for example 2018-04-01) = Sum of stock[qty] + sum of allocation[qty] (filtered on allocation[period] <=3).
For example we purchased 100 pcs and have 50 pcs on order in that period. The result would be 20+(-10+50-20+100-50) = 90 pcs.
and so on.
The matrix visual would then look like below if my example above was for the size S.
Size
PeriodStartDate S M L XL XXL XXXL
2017-10-31 10
2018-02-01 40
2018-04-01 90
How can I create a calculation for the availableQty that will be grouped in the correct way in my matrix visual depending on both articles[sizes] (columns) and "newTable"[periodStartDates] (rows) and show the correct results?
To answer your questions:
1. What fields do you use to create relationship among the above tables?
Please see above.
3. How do you get 2018-04-01 PeriodStartDate in the Matrix visual? I don't see this value in your allocation table.
This was just a fictive example so the numbers aren't correct as our allocation register have hundreds of tousands of rows.
3. How do you return the numerical values in the Matrix visual based on the above sample data?
This is what I need help with as I don't know how to make the correct calculation. Please see above what the correct result should look like.
Regards,
Patrik
@Guuuuschi,
When you filter Period to 1, the result should be 20+(-10) = 10 pcs, in your expected Martix visual, you put 10 under size S, but based on your sample data, the 10 pcs is for Size S and M.
When the Period<=2, the result would be 20+(-10+50-20) = 40 pcs. But in 2/1/2018, you have two P qty(20 and 50), the result should be 20+(-7-3-5-8+20+50), and this result is for Size S and M.
Could you please explain the above logic?
And I create the following measures, please check if they return your expected result.
stockqty = SUM(Stock[qty])
allocationqty = SUM(allocation[qty])
value = allocation[allocationqty]+Stock[stockqty]
Regards,
Lydia
Hi Lydia,
Sorry for late reply and thanks for your answer.
The calculations that you have done are not entirely correct. You have calculated each period separately. This works for the first period but for the subsequent periods you have to add the value from the previous periods.
The correct result for your example should be:
size S M
periodStartDate value allocationQty stockQty value allocationQty stockQty
2017-10-27 -2 -7 5 12 -3 15
2018-02-01 13 15 5 54 42 15
The calculation for value should be:
Size S Period 1
5+(-7) = -2
Size S Period 2
5+(-7)+(-5)+20 = 13
Size M Period 1
15+(-3) = 12
Size M Period 2
15+(-3)+(-8)+50 = 54
This is my first problem to do the above calculation correct.
The second one is when we don't have values for period 1 in the allocation table but still need to define period 1 as todays date. I will give you a new example.
articleNo modelColour size description
20206001 2020600 S Testarticle 1 600-black S
20206002 2020600 M Testarticle 1 600-black M
articleNo description qty warehouseCode
20206001 Testarticle 1 600-black S 10 2
20206001 Testarticle 1 600-black S 0 4
20206002 Testarticle 1 600-black M 20 2
articleNo period periodStartdate qty type warehouseCode
20206001 2 2018-02-01 -5 O 2
20206001 2 2018-02-01 -3 O 2
20206001 2 2018-02-01 40 P 2
20206001 3 2018-04-01 60 P 2
20206002 2 2018-02-01 -10 O 2
20206002 2 2018-02-01 70 P 2
20206002 3 2018-04-01 -20 O 2
20206002 3 2018-04-01 60 P 2
This should give the following matrix (assuming that "today" is 2017-11-13):
size S M
periodStartDate value allocationQty stockQty value allocationQty stockQty
2017-11-13 10 0 10 20 0 20
2018-02-01 42 32 10 80 60 20
2018-04-01 102 60 10 120 40 20
Regards,
Patrik
@Guuuuschi,
In your scenario, you would need to add blank rows for period 1 in allocation table.
Then create the following column, ALLqty measure, value measure in the allocation table. Other measures will be same as my previous reply.
Column = IF('allocation'[periodStartDate]=BLANK(),TODAY(),'allocation'[periodStartDate])
ALLqty= CALCULATE( SUM('allocation'[qty]),FILTER(ALL('allocation'[Column]),'allocation'[Column]<=MAX('allocation'[Column])))
value = 'allocation'[ALLqty]+'Stock'[stockqty]
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |