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.

CoreyP's avatar
CoreyP
Solution Sage
2 years ago

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' )