Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys!
I encountered a problem recently on one of my projects.
Basically, I'm creating a Usable Inventory Report based on data being submitted to us
In the raw file, we have the ff columns:
SKU Code | Inventory On Hand | Batch Code | Receipt Date | Final Usable Date |
20010G | 10 | 175261 | 01/15/2020 | 06/12/2020 |
What I would like to happen is to get the usable supply from and to those spLink to Sample Data ecified dates and lay it out on a full year basis.
Output as illustrated below:
Wk 3 - being the Receipt date
wk 24 - Final Usable Date
Wk 25 - unusable
wk 1 | ,,, | wk 3 | … | wk 24 | wk 25 | |
20010G | 0 | 0 | 10 | 10 | 10 | 0 |
Do you think this is possible?
Thank you!
Solved! Go to Solution.
Hi @JCPO ,
I created a dedicated calendar table with this DAX statement:
Calendar =
var DateStart = MIN('Sheet'[Receipt Date])
var DateEnd = MAX('Sheet'[Final Dispatch Date])
return
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR( DateStart , DateEnd )
, "weeknum iso" , WEEKNUM(''[Date] , 21)
, "year" , YEAR(''[Date])
)
, "year iso" ,
IF([weeknum iso] < 5 && WEEKNUM(''[Date]) > 50
, [year] + 1
, IF([weeknum iso] > 50 && WEEKNUM(''[Date]) < 5 ,
[year] - 1 ,
[year]
)
)
)
Then I expanded the existing table in your Excel sheet as follows:
Sheet Expanded =
GENERATE(
'Sheet'
, DATESBETWEEN('Calendar'[Date] , 'Sheet'[Receipt Date] , 'Sheet'[Final Dispatch Date] )
)
This expands the existing 26k rows to 12 million rows 🙂
I created a relationship between the calendar table and the "Expanded Sheet" table:
Note that the original table "Sheet" is hidden, as I no longer use this table for data visualization.
I created a measure:
Total Inventory =
SUMX(
VALUES('Sheet Expanded'[SKU Code])
, var _lastdate = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(SUM('Sheet Expanded'[Inventory]) , 'Calendar'[Date] = _lastdate)
)
This allows you to create a chart like this:
Here you will find the pbix:
https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EbvQUuS6RAdBh82ACfyELloBR1EOx...
Hopefully, this provides what you're looking for.
Best regards
Tom
Hi @JCPO ,
I created a dedicated calendar table with this DAX statement:
Calendar =
var DateStart = MIN('Sheet'[Receipt Date])
var DateEnd = MAX('Sheet'[Final Dispatch Date])
return
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR( DateStart , DateEnd )
, "weeknum iso" , WEEKNUM(''[Date] , 21)
, "year" , YEAR(''[Date])
)
, "year iso" ,
IF([weeknum iso] < 5 && WEEKNUM(''[Date]) > 50
, [year] + 1
, IF([weeknum iso] > 50 && WEEKNUM(''[Date]) < 5 ,
[year] - 1 ,
[year]
)
)
)
Then I expanded the existing table in your Excel sheet as follows:
Sheet Expanded =
GENERATE(
'Sheet'
, DATESBETWEEN('Calendar'[Date] , 'Sheet'[Receipt Date] , 'Sheet'[Final Dispatch Date] )
)
This expands the existing 26k rows to 12 million rows 🙂
I created a relationship between the calendar table and the "Expanded Sheet" table:
Note that the original table "Sheet" is hidden, as I no longer use this table for data visualization.
I created a measure:
Total Inventory =
SUMX(
VALUES('Sheet Expanded'[SKU Code])
, var _lastdate = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(SUM('Sheet Expanded'[Inventory]) , 'Calendar'[Date] = _lastdate)
)
This allows you to create a chart like this:
Here you will find the pbix:
https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EbvQUuS6RAdBh82ACfyELloBR1EOx...
Hopefully, this provides what you're looking for.
Best regards
Tom
This expands the existing 26k rows to 12 million rows
How long did this take to complete? I have an inventory table, but thinking about alterring the beginning dates (or null dates), per: https://community.powerbi.com/t5/Desktop/Modelling-Best-Practices-with-Multiple-Date-Columns-amp-Val...
4,000 items * Twenty years * 365 days = 29,200,000 rows.
Thank you so much @TomMartens !!!
Works like a charm but I still need to understand the DAX so I can apply in other problems.
Thanks again!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |