Need help in DAX

[Anonymous]

Hi,

I have one table in Power Bi model, which has year wise Audit information for each supplier and there might the situation where audit might get skip for a particular supplier.

Ex.

Supplier ID  Audit Date         Audit Score 

A                   2018-01-01        10

A                   2016-01-01          7

B                    2015-01-01         5

C                   2018-01-01         8

Now there will be one Year level drop for this report. so the reporting requirement is such way that whatever year I choose I need to have the latest audit score details for that supplier till that year.

Ex. For Year 2018

Supplier  Last Audit Date        Last  Audit Score 

A                   2018-01-01        10

B                    2015-01-01         5

C                   2018-01-01         8

Ex, for year 2017 it will be 

Supplier   Last Audit Date        Last  Audit Score

A                   2016-01-01          7

B                    2015-01-01         5

this table is connected to time and supplier dimension ...so Can I calculate the  Last Audit Date , Last  Audit Score dynamically?

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create a slicer table as below and then create a measure and use it in visual level filter. Here is the sample file.

Latest =
IF (
    MAX ( Data[Audit Date] )
        = CALCULATE (
            MAX ( Data[Audit Date] ),
            FILTER (
                ALLEXCEPT ( Data, Data[Supplier ID] ),
                YEAR ( Data[Audit Date] ) <= SELECTEDVALUE ( 'Table'[Year] )
            )
        ),
    "latest"
)

Regards,

Cherie

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.