Total between Two Different Dates in Different Columns

[Anonymous]

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!

Link to Sample data

@jdbuchanan71 

@v-eachen-msft 

[TomMartens · Accepted 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

[jaeiow]

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.

@Anonymous 

[Anonymous]

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!