Days between dates

[jake4001]

Hi everyone, I have a storage calendar where the customer is asking for a report that returns the number of days that containers were in storage within a date range. Here is the scenario:

Container A is in storage 11/01/2023 - 11/15/2023

Container B is in storage 11/13/2023 - 11/20/2023

Container C is in storage 11/7/2023 - 11/9/2023

The customer runs a report for 11/10/2023 - 11/14/2023. The result:

Container A = 5 days, Container B 2 day, Container C 0 days.

I want to be able to filter the report by any specified date ranges, so in this case 11/10/2023 - 11/14/2023. Does anyone have ideas on how to accomplish this?

Storage table

Container	In Date	Out Date
A	11/01/2023	11/15/2023
B	11/13/2023	11/20/2023
C	11/7/2023	11/9/2023

Calendar table (no relationship to Storage table)

Date	Year	Month	Month Name
11/1/2023	2023	11	November
11/2/2023	2023	11	November
11/3/2023	2023	11	November
etc	etc

I have a calculated measure for storage days, but it will only work if i create a table using the dates from the calendar table in one column then this measure in the next column. I can't choose 2 dates to count the number of storage days that occured during the time frame (since no relationship between tables)

StorageDays =

SUMX(

    COUNTROWS(

        FILTER(

            'Calendar',

            'Calendar'[Date] >= Storage[In Date] &&

            'Calendar'[Date] <= AllShipmentEvents[Out Date]

        )

    )

)

[v-zhangti · Accepted solution]

Hi, @jake4001 

You can try the following methods.
Table:

Calendar = CALENDAR(MIN('Storage'[In Date]),MAX('Storage'[Out Date]))

Measure:

Measure = 
Var _count1=CALCULATE(COUNT('Calendar'[Date]),FILTER(ALL('Storage'),[In Date]<=MIN('Calendar'[Date])&&[Out Date]>=MAX('Calendar'[Date])))
Var _count2=CALCULATE(COUNT('Calendar'[Date]),FILTER(ALL('Calendar'),[Date]>=SELECTEDVALUE('Storage'[In Date])&&[Date]<=MAX('Calendar'[Date])))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Storage'[In Date])<=MIN('Calendar'[Date])&&SELECTEDVALUE('Storage'[Out Date])>=MAX('Calendar'[Date]),_count1,
SELECTEDVALUE('Storage'[In Date])>=MIN('Calendar'[Date])&&SELECTEDVALUE('Storage'[In Date])<=MAX('Calendar'[Date])
                                                                    &&SELECTEDVALUE('Storage'[Out Date])>=MAX('Calendar'[Date]),_count2,
SELECTEDVALUE('Storage'[In Date])>=MAX('Calendar'[Date]),0,
SELECTEDVALUE('Storage'[Out Date])<=MIN('Calendar'[Date]),0)

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jake4001]

Hey @CoreyP , thank you. Unfortunately I have 2 million rows in my storage table, and due to data issues, a few of my In Dates are after my Out Dates so I'm not sure how to work around these things

[gmsamborn]

Hi @jake4001  @CoreyP 

I generated 5 spreadsheets (random) with more than a million rows each and imported them into Power Query and appended them. 

I have 5,198,000 rows in my Storage table and 235,962,908 rows in my generated table and with an OLD laptop with 16GB, there is no lag at all when changing slicers.

I wouldn't write off Corey's original idea/solution too quickly.

[CoreyP]

This seems to work. Just make sure your date table is disconnected.

Days in Storage ( in Filtered Period ) = 
VAR _startdate = IF( MIN( 'Date'[Date] ) > [From Date] , MIN( 'Date'[Date] ) , [From Date] )
VAR _enddate = IF( MAX( 'Date'[Date] ) < [To Date] , MAX( 'Date'[Date] ) , [To Date] )
VAR _days = DATEDIFF( _startdate , _enddate , DAY ) +1

RETURN
IF( _days >= 0 , _days , BLANK() )

[CoreyP]

New Table = GENERATE( 'Storage Table' , CALENDAR( [In Date] , [Out Date] ) )

Then establish a relationship from your date table to the date column of this new table. Write a new measure: Days in Storage = COUNTROWS( 'New Table' )

[Ahmedx]

can you share your real data? so I can understand what your model looks like

[jake4001]

hi @Ahmedx , i just shared real data. thank you