Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JCPO
Helper I
Helper I

Total between Two Different Dates in Different Columns

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 CodeInventory On HandBatch CodeReceipt DateFinal Usable Date
20010G1017526101/15/202006/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 3wk 24wk 25
20010G001010100

 

Do you think this is possible?

Thank you!

 

Link to Sample data

 

@jdbuchanan71 

@v-eachen-msft 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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:

image.png

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

@JCPO 

 

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.