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

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.