Calculating Days Based On Dates & Criteria

[SamFletch]

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 between 2 dates, with a slight twist. 

Let's give you an example of my data and I'll show you what I mean. So let's say I have a product that has a few dates, an "activated date" a "deactived date"  and a toggle as to whether it is currently active. It looks a little like this:

Product	Activate Date	Deactivate Date	Is Active
A	01/01/20	10/10/20	No
B	01/01/20	10/10/20	No
C	18/03/20		Yes
D	24/04/20		Yes
A	01/01/21		Yes
E	01/01/21	12/01/21	No
F	01/01/21	15/01/21	No
G	12/01/21		Yes
H	16/01/21		Yes

As you can see, a produce can have multiple days active (as in the case of product A that was active from from 01/01/20 until 10/10/20 then again from 01/01/21. Other than that, a product can be active or inactive at any given period.

I am trying to create a custom column which shows how many days in the current month that a product has been active which also assumes that any product currently active is going to run until the end of the month. The new column would look like the below:

Product	Activate Date	Deactive Date	Is Active	Days Active This Month
A	01/01/20	10/10/20	No	0
B	01/01/20	10/10/20	No	0
C	18/03/20		Yes	31
D	24/04/20		Yes	31
A	01/01/21		Yes	31
E	01/01/21	12/01/21	No	12
F	01/01/21	15/01/21	No	15
G	12/01/21		Yes	19
H	16/01/21		Yes	15

So looking it the new column, it assumes that anything active as of the 01/01/21(C & D in the table) are going to be active the full 31 days, any vehicles activated in January this year (A, G & H in the table) are going to run to the end of it and any that were deactived in January (E & F in the table) will only be active the number of days they were actually active for. 

I hope this all makes sense so far.

Now I wrote a piece of DAX that I am actually pretty proud of but is not quite working right: 

Days Active This Month = 

SWITCH( 
True(),
 'Product'[Is Active] = "Yes" && MONTH('Product'[Activate Date]) <> MONTH (MAX('Product'[Activate Date])), DATEDIFF (STARTOFMONTH (TODAY()), ENDOFMONTH (TODAY()), DAY),
 'Product'[Is Active] = "Yes" && MONTH('Product'[Activate Date]) = MONTH ( MAX('Product'[Activate Date])), DATEDIFF ( 'Product'[Activate Date], ENDOFMONTH (TODAY()), DAY ),
 'Product'[Is Active] = "No" && MONTH('Product'[Activate Date]) = MONTH ( MAX( MAX('Product'[Activate Date])), DATEDIFF ('Product'[Activate Date], 'Product'[Deactivate Date] , DAY ),
 'Product'[Is Active] = "No" && MONTH('Product'[Activate Date]) <> MONTH ( Max('Product'[Activate Date])), DATEDIFF ( STARTOFMONTH(TODAY()), 'Product'[Deactivate Date], DAY),
0
)

This feels like it is on the right track but the results are just not coming back quite right. EDIT: Link to google doc with results

Firstly, I know that I'm doing wrong with the way I'm calculating if the product was active at the start of the month or not. MONTH just doesn't cut it but I'm struggling to write the formula to figure out  if the product was active at the start of the month or not.

Other than that though, I feel like it should be working but I'm not confident it is.

Any help with this is greatly appreciated, I hope I've made myself clear but I can understand that I may not have, so any questions, please feel free to send my way. 

Thank you.

[Fowmy · Accepted solution]

@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]

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]

@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])
                )
            )
    )
)

[vanessafvg]

when you say the results are not coming back right can you give an example of what is not right.  

I would also probably write it like this

Days Active This Month =
SWITCH (
    TRUE (),
    AND (
        'Product'[Is Active] = "Yes",
        MONTH ( 'Product'[Activate Date] ) <> MONTH ( MAX ( 'Product'[Activate Date] ) )
    ), DATEDIFF ( STARTOFMONTH ( TODAY () ), ENDOFMONTH ( TODAY () ), DAY ),
    AND (
        'Product'[Is Active] = "Yes",
        MONTH ( 'Product'[Activate Date] ) = MONTH ( MAX ( 'Product'[Activate Date] ) )
    ), DATEDIFF ( 'Product'[Activate Date], ENDOFMONTH ( TODAY () ), DAY ),
    AND (
        'Product'[Is Active] = "No",
        MONTH ( 'Product'[Activate Date] ) = MONTH ( MAX ( 'Product'[Activate Date] ) )
    ), DATEDIFF ( 'Product'[Activate Date], 'Product'[Deactivate Date], DAY ),
    AND (
        'Product'[Is Active] = "No",
        MONTH ( 'Product'[Activate Date] ) <> MONTH ( MAX ( 'Product'[Activate Date] ) )
    ), DATEDIFF ( STARTOFMONTH ( TODAY () ), 'Product'[Deactivate Date], DAY ),
    0
)

[SamFletch]

Sorry I didn't include this at the time. I've downloaded a full report of what the numbers look like and as you can see, they're just all over the place!

I've pasted the results into a Google Sheet which can you access with the link below:

Link to Doc