How to create a assessments table due for review based on historical data

[nailifarhanah]

Hi, 

I have a data table like that list down all the assessments done by each companies up until 2023

Tier	Company	Year	Assessment
Class I	A	2019	Yes
Class I	A	2020	Yes
Class I	A	2023	Yes
Class II	B	2020	Yes
Class II	B	2021	Yes
Alpha	C	2021	Yes
Alpha	C	2022	Yes
Alpha	C	2023	Yes

Ultimately, I want to create a table that will show when a company has [reviously gone for assessments and which company is due to go for review in 2024 and 2025 based on their tier type. The rule is for Alpha type companies, it has to go each year, for Class I, its every 2 years, for Class II its every 3 years. The table that I want will look like this:

Company	2019 (Historic data)	2020 (Historic data)	2021 (Historic data)	2022 (Historic data)	2023 (Historic data)	Due in 2024	Due in 2025
A	Yes	Yes			Yes		Yes
B		Yes	Yes			Yes
C			Yes	Yes	Yes	Yes	Yes

How do I create this kind of table in PowerBI? Really appreciate the help.

Thank you

[techies]

Hi @nailifarhanah can you pls share the screenshot of your data model so we can see how relationships are set up. Also i guess you might need to create Company years tables with all possible Company tier year combination like this

CompanyYears =

CROSSJOIN (

    DISTINCT ( SELECTCOLUMNS ( Sheet1, "Company", [Company], "Tier", [Tier] ) ),

    DISTINCT ( SELECTCOLUMNS ( 'date', "Year", [Year] ) )

)

[Irwan]

hello @nailifarhanah 

you can do this in matrix visual.

Hope this will help.

Thank you.