LOOKUPVALUE with multiple conditions in one table

[helen_brunyee95]

Hello Everyone,

I have been trying to work this out for several days now and can't quite get it.

I have a "audit" table which records/logs the status of users each month (so essentially, every month it looks at all the User IDs and makes a note/takes a snapshot of the status is was that month, along with the date (last day of the month)).  Example of the table:

UserID	Status	SnapshotDate	IsInLastFY?	Status Prev FY (end)
D1234	ACT	31/03/2024	Y	ACT
D1234	DIS	29/02/2024	N	ACT
D5678	DIS	30/09/2022	N	DIS
D5678	ACT	31/12/2023	Y	DIS
D5678	DIS	31/03/2024	Y	DIS
D1234	ACT	31/05/2024	N	ACT

What I am trying to do is work out what the status was for that user the last month of the previous financial year, for e.g. when looking at the report in June 2024, this column would show the status for that user in March 2023. (It needs to be as a column in the table due to how I am going on to use this field)

I have managed it when looking back previous months, using columns such as:

The issue I am having is having seems to come from me wanting to lookup that status, using the userId AND the date being in last FY (using my FY flag column) AND it being the last record from the last FY.

Hopefully that all makes sense!

Thank you so much in advance, any help is greatly appreciated! 🙂

Best,

Helen

[v-huijiey-msft]

Hi @helen_brunyee95 ,

Thanks for the reply from MFelix .

I use your sample data and create a measure:

Status Prev FY (end) =
VAR _vtable =
    FILTER (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[IsInLastFY?] = "Y" ),
            "_MaxDate",
                CALCULATE (
                    MAX ( 'Table'[SnapshotDate] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                    )
                )
        ),
        'Table'[SnapshotDate] = [_MaxDate]
    )
RETURN
    CONCATENATEX (
        FILTER ( _vtable, [UserID] = SELECTEDVALUE ( 'Table'[UserID] ) ),
        [Status]
    )

Now you can realize your red column.

The final page visual effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[helen_brunyee95]

Hi @v-huijiey-msft 

Thank you so much for getting backt o me and working this out - it is great & does what I am trying to calculate.

Is there a way to get this as a column though? As a column, I will be able to then count the number of each status in a card visual, for example. As a measure, I wouldn't be able to do that.

Thanks again for all your help, I really apprecaite it.

Best,

Helen

[v-huijiey-msft · Accepted solution]

Hi @helen_brunyee95 ,

Ok, please try this calculated column syntax:

_Status Prev FY (end) = 
VAR _currentID = 'Table'[UserID]
VAR _vtable =
    SELECTCOLUMNS(FILTER (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[IsInLastFY?] = "Y" ),
            "_MaxDate",
                CALCULATE (
                    MAX ( 'Table'[SnapshotDate] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                    )
                )
        ),
        'Table'[SnapshotDate] = [_MaxDate]
    ),"_ID",'Table'[UserID],"__Status",'Table'[Status])
RETURN
    CONCATENATEX (
        FILTER ( _vtable, [_ID] = _currentID ),
        [__Status]
    )

The final page visual effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[MFelix]

Hi @helen_brunyee95 ,

Taking you example into account the information believe is not matching you refer that it should pick up the last financial year information in your example is June 2024 correspond to March 2023 how do we know what is the period we should look at?

On second place also looking at the data you gave how is final column getting values? Again the dates for me are not making any sense.

I have sorted the information by user and date and not abble to understand how the status is ACT or DIS for each user.