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.
I am trying to measure beginning inventory for a Month or Quarter. The difficulty is that in any given week I do not necessarilly have data (see table) so I woud like to summ across the "first reported" week. Is there a way to do this with measures and/or DAX? THANKS
qtr 1 | qtr 1 | qtr 1 | qtr 1 | qtr 1 | ||
customer name | SKU | week 1 | week 2 | week 3 | week 4 | week 5 |
Customer 1 | SKU 1 | 1 | 1 | |||
SKU 2 | 1 | |||||
SKU 3 | 1 | |||||
SKU 4 | 1 | |||||
Customer 2 | SKU1 | 1 | 1 | 1 | ||
Customer 3 | SKU1 | 1 | ||||
SKU 1 | 1 | |||||
SKU 2 | 1 | |||||
SKU 3 | 1 | |||||
Customer 4 | SKU 1 | |||||
Customer 5 | SKU 2 | 1 | 1 | 1 | ||
Customer 6 | SKU 3 | 1 | ||||
Customer 7 | SKU 3 | 1 | 1 | |||
Customer 8 | SKU 2 | 1 | 1 | 1 | ||
Customer 9 | SKU 1 | |||||
Customer 10 | SKU 1 | 1 | 1 | 1 | 1 | |
Beginning Value for qtr 1 = Sum of red = 14 |
Do You have some Sample data.
I have done this before the idea is to Calculate toe sum or values for each week, find the first week and return this value. these two parts are relativly simple but it is dependent on how your data is structured in the data table.
for example if you have two colunms of Week and Qrt in your table then this complicates things as you cannot use time based functions.
please add a sample data set and i can take a look for you.
Proud to be a Super User!
Thanks Anthony - we are the more complicated 🙂 ... here is some sample data:
Region | Reporting Customer | Product SKU | Year | Week Number | Month Number | Qtr Number | Sales Quantity |
Region A | Customer 1 | SKU 1 | 2018 | 50 | 12 | 4 | 42 |
Region A | Customer 1 | SKU 1 | 2018 | 51 | 12 | 4 | 2 |
Region A | Customer 1 | SKU 2 | 2018 | 51 | 12 | 4 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 2 | 1 | 1 | 42 |
Region A | Customer 1 | SKU 1 | 2019 | 3 | 1 | 1 | 41 |
Region A | Customer 2 | SKU 1 | 2019 | 4 | 1 | 1 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 4 | 1 | 1 | 41 |
Region A | Customer 1 | SKU 1 | 2019 | 5 | 1 | 1 | 1 |
Region A | Customer 1 | SKU 2 | 2019 | 5 | 2 | 1 | 40 |
Region A | Customer 2 | SKU 1 | 2019 | 5 | 1 | 1 | 4 |
Region A | Customer 3 | SKU 3 | 2019 | 6 | 2 | 1 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 6 | 2 | 1 | 40 |
Region A | Customer 1 | SKU 1 | 2019 | 7 | 2 | 1 | 40 |
Region A | Customer 2 | SKU 1 | 2019 | 7 | 2 | 1 | 4 |
Region A | Customer 1 | SKU 1 | 2019 | 8 | 2 | 1 | 40 |
Region A | Customer 2 | SKU 1 | 2019 | 8 | 2 | 1 | 8 |
Region A | Customer 3 | SKU 3 | 2019 | 9 | 2 | 1 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 9 | 3 | 1 | 40 |
Region A | Customer 2 | SKU 1 | 2019 | 9 | 2 | 1 | 3 |
Region B | Customer 4 | SKU 1 | 2019 | 9 | 2 | 1 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 10 | 3 | 1 | 40 |
Region B | Customer 4 | SKU 1 | 2019 | 10 | 3 | 1 | 1 |
Region A | Customer 1 | SKU 1 | 2019 | 11 | 3 | 1 | 40 |
Region A | Customer 2 | SKU 1 | 2019 | 11 | 3 | 1 | 3 |
Region B | Customer 4 | SKU 1 | 2019 | 11 | 3 | 1 | 1 |
HI @H20treddr ,
You can consider to write a rolling total formula to summary multiple date periods.
DAX 101: Computing running totals in DAX
Regards,
Xiaoxin Sheng
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |