Need help in DAX

Question

Hi,I have one table in Power Bi model, which has year&nbsp;wise Audit information for each supplier and there might the situation where audit might get skip for a particular&nbsp;supplier.Ex.Supplier ID&nbsp; Audit Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Audit Score&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018-01-01&nbsp; &nbsp; &nbsp; &nbsp; 10A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2015-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp;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 2018Supplier&nbsp; Last Audit Date&nbsp; &nbsp; &nbsp; &nbsp; Last&nbsp; Audit Score&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018-01-01&nbsp; &nbsp; &nbsp; &nbsp; 10B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2015-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp;Ex, for year 2017 it will be&nbsp;&nbsp;Supplier&nbsp; &nbsp;Last Audit Date&nbsp; &nbsp; &nbsp; &nbsp; Last&nbsp; Audit ScoreA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2015-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;this table is connected to time and supplier dimension ...so Can I calculate the&nbsp;&nbsp;Last Audit Date , Last&nbsp; Audit Score dynamically?

v-cherch-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;You may create a slicer table as below and then create a measure and use it in visual level filter.&nbsp;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] ) &lt;= SELECTEDVALUE ( 'Table'[Year] )
            )
        ),
    "latest"
)Regards,Cherie

Forum Discussion

Need help in DAX

2 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX