Forum Discussion

jake4001's avatar
jake4001
Frequent Visitor
2 years ago
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...
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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's avatar
jake4001
Frequent Visitor
2 years ago

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

  • CoreyP's avatar
    CoreyP
    Solution Sage
    2 years ago

    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() )
  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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.