Check If Event Happened In Given Month

[Anonymous]

I have a table of events with start and end dates based on company:

EventID	CustomerID	StartDate	EndDate	Status
1	1	12/20/19	2/12/20	Bad
2	1	3/12/20	3/29/20	Bad
3	2	3/1/20	3/4/20	Good
4	2	1/4/20	1/29/20	Bad
5	3	2/6/20	2/21/20	Good
6	3	2/24/20	3/3/20	Bad

I then have a table that's connected by customer and month, and I want to create a calculated column (call it BadStatus below) to show EACH month where they had at least one actively "Bad" status. The results given the data above should look like this:

Customer	Month	BadStatus
1	1/1/20	Bad
1	2/1/20	Bad
1	3/1/20	Bad
2	1/1/20	Bad
2	2/1/20
2	3/1/20
3	1/1/20
3	2/1/20	Bad
3	3/1/20	Bad

Let me know if you need any other info for guidance. Thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

You can create a measure like so:

Measure BadStatus = 
VAR t =
    CROSSJOIN (
        SUMMARIZE (
            Events,
            Events[CustomerID],
            Events[StartDate],
            Events[EndDate],
            Events[Status]
        ),
        SUMMARIZE ( Customers, Customers[Month] )
    )
VAR t2 =
    FILTER (
        t,
        [Month]
            >= EOMONTH ( Events[StartDate], -1 ) + 1
            && [Month]
                <= EOMONTH ( Events[EndDate], -1 ) + 1
            && [Status] = "Bad"
    )
RETURN
    MAXX (
        FILTER (
            t2,
            [CustomerID] = MAX ( Customers[Customer] )
                && [Month] = MAX ( Customers[Month] )
        ),
        [Status]
    )

Or, create calculated column based on a calculated table like so:

Table =
VAR t =
    CROSSJOIN (
        SUMMARIZE (
            Events,
            Events[CustomerID],
            Events[StartDate],
            Events[EndDate],
            Events[Status]
        ),
        SUMMARIZE ( Customers, Customers[Month] )
    )
VAR t2 =
    FILTER (
        t,
        [Month]
            >= EOMONTH ( Events[StartDate], -1 ) + 1
            && [Month]
                <= EOMONTH ( Events[EndDate], -1 ) + 1
            && [Status] = "Bad"
    )
RETURN
    SUMMARIZE ( t2, [CustomerID], [Month], [Status] )

BadStatus = 
LOOKUPVALUE (
    'Table'[Status],
    'Table'[CustomerID], Customers[Customer],
    'Table'[Month], Customers[Month]
)

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, Icey! Had to throw a NOT(ISBLANK()) condition for the Status as some Statuses were blank in my dataset but everything else worked great. Appreciate the help.