Get Tenant Status by (user's selected if applicable) Latest Month

[JustDavid]

Power BI / DAX Experts,

Attached is my latest power bi sample and below is a screenshot of a sample.

Note here that in the original file, the view is transpose. In other words, the tenants which is shown in print screen puts in columns, in my original file, it's put in the row section.

First of all, I'd like to say that screenshot is showing just the sample of data from "beginning of time" to current latest date. "beginning of time" differs from tenant to tenant. For example, tenant 25801 (#1) and 56916 (#3) beginning of time is Jan 2023, whereas tenant 56628 (#2) and 57008 (#5) beginning of time is Jun 2024.

However, for the purpose of explanation and to show you what's my desire, I'd ASSUME that a user has click on 1 or more slicer values, may it be continuous or non-continuous month range, and that the LATEST selected date filter was Jun 2025 (thus the highlighted yellow)

Thus from this explanation onwards, the "constraints" would be the followings:

1. Programme will ALWAYS need to be 'MNP', 'PUB' or 'REG'.
2. Assume that user's date selection can be single or multiple (continuour or non-continuous) values where for this purpose, Jun 2025 was selected and it's the latest month from the selection(s).

What I'm trying to accomplish:

I'm trying to list tenants where their status IS NOT 'RGI Rent' with an EXCEPTION where status/text contains 'Vacant' WHERE the above constraints were stated.

Logic to determine the output:

Example 1 (Tenant 25801):

To recap, user's date slicer's latest date is Jun 2025.

First, we can see that, tenant's beginning of time was Jan 2023 and that it's status was 'Market Rent' up to Jan 2024.

Second, tenant status change to 'RGI Rent' starting Feb 2024 till Jan 2025.

Third, status change to (coincidentally) 'Market Rent' from Feb 2025 to user's latest slicer date value, Jun 2025.

Verdict: Need to list this tenant since as of Jun 2025, tenant is NOT 'RGI Rent' PROVIDED that Programme is 'MNP', 'PUB' or 'REG'

Example 2 (Tenant 56628):

To recap, user's date slicer's latest date is Jun 2025.

First of all, we can see that we do not have any data from Jan 2023 to May 2024.

Secondly, we can see that we have data starting at Jun 2024 and has text CONTAINS 'Vancant'. In this case, we ignore it.

Third, we can see that, tenant's beginning of time (ignoring blanks or vacant) was Aug 2024 and that it's status was 'RGI Rent' till Sep 2024. Status change to a text that contains 'Vacant' from Oct 2024 to user's latest slicer date value, Jun 2025.

Verdict: DO NOT need to list this tenant since as of Jun 2025, tenant has moved out and thus has text contains 'Vacant'

Example 3 (Tenant 56725):

To recap, user's date slicer's latest date is Jun 2025.

First of all, we can see that we do not have any data from Jan 2023 to July 2024.

Secondly, we can see that we have data starting at Aug 2024 and it's also tenant's beginning of time and that it's status was 'RGI Rent' till May 2025. Starting Jun 2025, it's status change to 'Market Rent'

Verdict: Need to list this tenant since as of Jun 2025, tenant status is NOT 'RGI Rent' PROVIDED that Programme is 'MNP', 'PUB' or 'REG'

Example 4 (Tenant 56916):

To recap, user's date slicer's latest date is Jun 2025.

First of all, we can see that, tenant's beginning of time was Jan 2023, however, it's text contains 'Vacant'. Thus we can ignore it.

Secondly, there isn't data from Mar 2023 to Aug 2024, Thus we can also ignore it. We can also ignore the Sep 2024, as it contains 'Vacant'.

Third, we can see tenant's beginning of time and that it's status was 'Rent Supp' starting Oct 2024 till Nov 2024.

Fourth, starting Dec 2024 till Jun 2025, it's status change to 'RGI Rent'

Verdict: DO NOT need to list this tenant since as of Jun 2025, tenant's status is 'RGI RENT' PROVIDED that Programme is 'MNP', 'PUB' and 'REG'

Example 5 (Tenant 57008):

To recap, user's date slicer's latest date is Jun 2025.

First of all, we can see that we do not have any data from Jan 2023 to Apr 2024.

Secondly, data starts from Jun 2024, however it contains 'Vacant'. Thus we can also ignore it from Jun 2024 to Oct 2024.

Third, we can see tenant's beginning of time and that it's status was 'RGI Rent' starting Nov 2024 till Jun 2025.

Verdict: DO NOT need to list this tenant since as of Jun 2025, tenant's status is 'RGI RENT' PROVIDED that Programme is 'MNP', 'PUB' and 'REG'

Hopefully given the examples and explanations above, you have a good idea of what i'm trying to accomplish.

Tenant Status Sample PBIX file 

[JustDavid]

Sorry for not being clear and cause confusion.

The print screen that I had is the whole of sample data NOT the end result that I wish to accomplish.

Coming back to my desired result, ASSUME if a user click on a slicer date whether it's 1 value or more, and whether it's continuous or non-continuous, and one of the value is LATEST date is Jun 2025, ALONG with a 'Programme' slicer, in which it's either 'MNP', 'PUB' or 'REG', I want the result to ONLY spits out the tenant ID WHERE it's NOT 'Rent RGI'.

So in my case example, it should ONLY spits out tenant 25801 and 56725

[v-achippa]

Hi @JustDavid,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response. 

Based on your requirement, please use this below measure that checks each tenant’s latest status as of the max selected date in the slicer:

ShowTenant =
VAR MaxSelectedDate = MAX ( 'Calendar'[Date] )
VAR TenantId = SELECTEDVALUE ( Tenant[TenantID] )
VAR LastDateForTenant = CALCULATE (MAX ( TenantStatus[Date] ),
TenantStatus[TenantID] = TenantId,
TenantStatus[Date] <= MaxSelectedDate,
REMOVEFILTERS ( TenantStatus[Date] )
)
VAR LatestStatus = CALCULATE (SELECTEDVALUE ( TenantStatus[Status] ),
TenantStatus[TenantID] = TenantId,
TenantStatus[Date] = LastDateForTenant,
REMOVEFILTERS ( TenantStatus[Date] )
)
VAR StatusText = UPPER ( TRIM ( LatestStatus ) )
RETURN
IF (
ISBLANK ( StatusText ),
0,
IF (
CONTAINSSTRING ( StatusText, "VACANT" ) || CONTAINSSTRING ( StatusText, "RGI" ),
0,
1
)
)

Add this measure to your visual and set a visual-level filter for ShowTenant = 1
It will return only tenants 25801 and 56725.

Thanks and regards,

Anjan Kumar Chippa

[JustDavid]

@v-achippa Thank you for replying.

However, I don't understand your DAX here.

From the VAR that you had, it seems that you assumed I have different tables:

• Calendar
• Tenant
• TenantStatus

But in my sample and attached pbix file, I only have 1 factTable.

VAR MaxSelectedDate = MAX ( 'Calendar'[Date] )
VAR TenantId = SELECTEDVALUE ( Tenant[TenantID] )
VAR LastDateForTenant = CALCULATE (MAX ( TenantStatus[Date] ),

Would you be able to modify your DAX as per my sample pbix file?

Thank you

[v-achippa · Accepted solution]

Hi @JustDavid,

Thank you for clarifying that everything is in a single fact table. Based on your requirement, please try the below measure:

ShowTenant =

VAR MaxSelectedDate = MAX ( 'FactTable'[Date] )

VAR TenantId = SELECTEDVALUE ( 'FactTable'[TenantID] )

VAR LastDateForTenant =

CALCULATE (

MAX ( 'FactTable'[Date] ),

'FactTable'[TenantID] = TenantId,

'FactTable'[Date] <= MaxSelectedDate,

REMOVEFILTERS ( 'FactTable'[Date] )

)

VAR LatestStatus =

CALCULATE (

SELECTEDVALUE ( 'FactTable'[Status] ),

'FactTable'[TenantID] = TenantId,

'FactTable'[Date] = LastDateForTenant,

REMOVEFILTERS ( 'FactTable'[Date] )

)

VAR StatusText = UPPER ( TRIM ( COALESCE ( LatestStatus, "" ) ) )

RETURN

IF (

StatusText = "",

0,

IF (

NOT CONTAINSSTRING ( StatusText, "RGI" ) &&

NOT CONTAINSSTRING ( StatusText, "VACANT" ),

1,

0

)

)

Add this measure to your visual and set a visual-level filter for ShowTenant = 1

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @JustDavid,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @JustDavid,

We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[lbendlin]

Not sure I understand what you are trying to achieve. Something like this?