Count Companies with a Category Expired

[BotBot1]

Hi,

I am trying to get a measure that is a distinct count of company_ids where they have at least 1 expired insurance document. It needs to only look at insurances with a record status of 1, and for it to look at the latest date of each insurance type (as they can have multiple of the same type with different dates) and count the company if it is expired.

So the count would be companies with an active status where at least 1 insurance type (given by the latest date of each category) is before today. For example:

company_id 19451 and 44074 would be counted.

company_id 1152 would not be included, as they have renewed their insurance type.

Here are the relevant fields:

vw_FT_Company_Insurance_BI[Company_id]

vw_FT_Company_Insurance_BI[insurance_recordStatus]

vw_FT_Company_Insurance_BI[Insurance_Type_Name]

vw_FT_Company_Insurance_BI[ExpiryDate]

Please let me know if any further information is required, thank you.

[johnt75]

Try

Num expired =
VAR SummaryTable =
    INDEX (
        1,
        FILTER (
            vw_FT_Company_Insurance_BI,
            vw_FT_Company_Insurance_BI[insurance_recordStatus] = 1
        ),
        ORDERBY ( vw_FT_Company_Insurance_BI[ExpiryDate], DESC ),
        PARTITIONBY ( vw_FT_Company_Insurance_BI[Company_id],
        vw_FT_Company_Insurance_BI[Insurance_Type_Name] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( vw_FT_Company_Insurance_BI[Company_id] ),
        SummaryTable
    )
RETURN
    Result

[tamerj1 · Accepted solution]

Hi @BotBot1 
Please try

Count =
COUNTROWS (
    FILTER (
        GROUPBY (
            SUMMARIZE (
                FILTER (
                    vw_FT_Company_Insurance_BI,
                    vw_FT_Company_Insurance_BI[insurance_recordStatus] = 1
                ),
                vw_FT_Company_Insurance_BI[Company_id],
                vw_FT_Company_Insurance_BI[Insurance_Type_Name],
                "@ExpiryDate", MAX ( vw_FT_Company_Insurance_BI[ExpiryDate] )
            ),
            vw_FT_Company_Insurance_BI[Company_id],
            "@MinExperyDate", MINX ( CURRENTGROUP (), [@ExpiryDate] )
        ),
        [@MinExperyDate] <= TODAY ()
    )
)

[BotBot1]

Perfect, thank you so much @tamerj1