Check If Event Happened In Given Month

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.