Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find values NOT present in a category

I have a dynamic list of products what were sold in every month.

I'd like to return those products only which were not sold in that specific month.

If I had four products and A, B, C were sold in April, I'd like to return D for April.   

 

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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