retrieve latest non blank value if balnk value is selected

[Sri57]

I have a table Test with the columns Name, Rating, and Year. I'm using a Year slicer. If I select the value 2023/24 from the slicer, then I should get the count of records from that year. If there is no data for 2023/24, it should check for the previous year, 2022/23. If there is also no data for 2022/23, it should check for 2021/22, and so on, until it finds data, and get the count of records for the selection of 2023/24.
I tried to implement the logic, but I failed to get it exactly right. For example, when I select 2023/24 and there is no data for it, we get no records. Instead, the measure needs to retrieve the count of records for 2022/23.
I have attached the sameple pbix file
https://www.dropbox.com/scl/fi/304a4rxdnmsb96ndgri5m/test1.pbix?rlkey=cv14d4zsqbsjp0m22y89h040o&st=d...

TIA

[v-yajiewan-msft · Accepted solution]

Hi @Sri57, @aduguid,

Thank you for your prompt reply!

To meet your requirements, it’s necessary to use a name slicer to replace the name filter in table visual.

Then we need to add the Fiscal Year slicer from another table: Calendar to avoid blank value result.

Lastly, create a new measure in Audit table and drag the measure in visual filter to control how visual display latest non blank value:

Measure = 
var _slicerYear=SELECTEDVALUE('Calendar'[Fiscal Year])
var _FilterCount=COUNTROWS(FILTER(ALLSELECTED('Audit'),[FISCAL_YEAR]=_slicerYear))
var _beforeYear=MAXX(FILTER(ALLSELECTED('Audit'),[FISCAL_YEAR]<=_slicerYear),[FISCAL_YEAR])
RETURN SWITCH(TRUE(),
ISFILTERED('Calendar'[Fiscal Year])=FALSE(),1,
_FilterCount=BLANK() && MAX('Audit'[FISCAL_YEAR])=_beforeYear,1,
_FilterCount <> BLANK() && MAX('Audit'[FISCAL_YEAR])= _slicerYear,1,0)

Result for your reference:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Give this one a try. 

Count Records = 
VAR SelectedYear = MAX('Calendar'[Year])
VAR CheckYear = 
    CALCULATE(
        MAX('Test'[Year]),
        FILTER(
            'Test',
            'Test'[Year] <= SelectedYear
        )
    )
VAR ResultCount = 
    CALCULATE(
        COUNTROWS('Test'),
        FILTER(
            'Test',
            'Test'[Year] = CheckYear
        )
    )
RETURN
    ResultCount