Forum Discussion
Days between dates
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)
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.
7 Replies
can you share your real data? so I can understand what your model looks like
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' )
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() )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.
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.
