The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
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
Hi,
You may download my PBI file from here.
Hope this helps.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.
User | Count |
---|---|
137 | |
108 | |
80 | |
69 | |
59 |
User | Count |
---|---|
258 | |
128 | |
116 | |
100 | |
80 |