Forum Discussion
Solved
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: Contain...
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.
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
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() )