Total between Two Different Dates in Different Columns

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/EbvQUuS6RAdBh82ACfyELloBR1EOxXCaIWhQDYluT0KsEw?e=AhlXgM

Hopefully, this provides what you're looking for.

Best regards
Tom