Forum Discussion

SamFletch's avatar
SamFletch
Icon for Helper I rankHelper I
5 years ago
Solved

Calculating Days Based On Dates & Criteria

Hello all, I've been scratching my head with a problem for a few days now and I just can't seem to figure it out, so I was hoping you could help.   I'm trying to calculate how many days there are b...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    SamFletch 

    Add the following code as a new column to your table. Check with other scenarios and reply with your findings.

    Days Active This Month = 
var _thismonth = (EOMONTH(TODAY(),0))
var _active = T3[Is Active] = "Yes"
return
IF(
    _active,
   Min(MIN( _thismonth - T3[Activate Date] + 1 , _thismonth ), DAY(_thismonth)),
   Max(0,T3[Deactivate Date] - (_thismonth - DAY(_thismonth)) )   
)

     

     

     



Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

SamFletch 

Add the following code as a new column to your table. Check with other scenarios and reply with your findings.

Days Active This Month = 
var _thismonth = (EOMONTH(TODAY(),0))
var _active = T3[Is Active] = "Yes"
return
IF(
    _active,
   Min(MIN( _thismonth - T3[Activate Date] + 1 , _thismonth ), DAY(_thismonth)),
   Max(0,T3[Deactivate Date] - (_thismonth - DAY(_thismonth)) )   
)

 

 

 



  • SamFletch's avatar
    SamFletch
    Icon for Helper I rankHelper I
    5 years ago

    Wow this worked and you managed to do it in less than 10 lines of code! 

     

    Thank you so much, I'm very impresed and incredibly grateful! Would you mind explaining a little how it works? I understand the creation of the variables and honestly should have thought about doing that myself and I didn't realise EOMONTH was a thing, I'm just struggling to understand how the MIN and MAX lines are working. 

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      SamFletch 

      I noted that in my formula, I did not account for Activation dates within the month for Status NO, try this code instead of the above.

      Days Active This Month = 
var _thismonth = (EOMONTH(TODAY(),0))
var _active = T3[Is Active] = "Yes"
return
IF(
    _active,
   MIN(
       _thismonth - T3[Activate Date] + 1 , 
       DAY(_thismonth)
    ),
    IF(
        ISBLANK( T3[Deactivate Date] ), 
            0 , 
            COUNTROWS(
                INTERSECT(
                        CALENDAR(EOMONTH(_thismonth,-1),_thismonth),
                        CALENDAR(T3[Activate Date],T3[Deactivate Date])
                )
            )
    )
)