topic Re: How to create a assessments table due for review based on historical data in DAX Commands and Tips

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

nailifarhanah — Tue, 12 Aug 2025 03:01:26 GMT

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

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

Irwan — Tue, 12 Aug 2025 04:43:58 GMT

hello nailifarhanah

you can do this in matrix visual.

Hope this will help.

Thank you.

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

techies — Tue, 12 Aug 2025 07:21:43 GMT

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] ) )

)

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

v-prasare — Mon, 18 Aug 2025 04:53:53 GMT

Hi nailifarhanah,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Best Regards,
Prashanth Are

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

v-prasare — Mon, 25 Aug 2025 09:03:00 GMT

Hi nailifarhanah,

Best Regards,
Prashanth Are

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

v-prasare — Mon, 01 Sep 2025 08:12:29 GMT

May I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.