Active and Expired count based on the ID column basis on the date column

[robleek100]

Hi Team,

I have calculated the status for each row in my dataset basis on the date column. however I am not able get the Active and Expired count basis on the Date column which prior today's date.

I need your help in calculating Active and expired status basis on the today's date if any value  which is prior to today's date should be considered as "Expired" basis on the supplier ID column.  In my data set I have duplicate supplier ID however dates are different.

I want to calculate the status which is Expired & Active for any supplier ID It should be considered as expired only.

Only if all the status are active then it should be considered as active. My supplier Id should be counted only once as distinct.

Help needed...!

Thanks,

Rob Lee

[v-yulgu-msft]

Hi @robleek100,

You can add below measure into a Card visual.

count supplier =
CALCULATE (
    DISTINCTCOUNT ( Tab2[SupplierID] ),
    FILTER (
        ALL ( Tab2 ),
        CALCULATE (
            DISTINCTCOUNT ( Tab2[Status] ),
            ALLEXCEPT ( Tab2, Tab2[SupplierID] )
        )
            = 1
    )
)

Best regards,

Yuliana Gu

[robleek100]

Thank you! Yuliana for providing the solution to me. However provided solution still not meet my requirement.

It giving me status for single supplier id where it has two statas. But I want if any suppiler ID is having two status then it should considered only "Expired" not the active.

If put this number on cards visuals then I should get count of Active and Expired separately.

Explain it further If any supplier ID which got expired status then it should ignore same supplier ID Active status. It means supplier should have only one status. If it's Epxired then is should not be counted in Active again. Current query it getting counted in both Active and Expired due which my status count is not matching.

Thanks,

Rob

[robleek100]

Provided solution is partially correct not giving complete result. Hence, do share the complete solution of my query.

If you find provided query is not clear enough for you to provide the solution do let me know. I would definitely re-phrase my sentence to share my problem statement.

I appreciate your help.

Thanks

Rob

[robleek100]

Anybody on the Power BI community can help to achieve my result. I would be glad if anyone can provide solution to my query.

Thanks,

Rob