DAX Query

[Sri57]

Hello Experts,
I want to write a dax measure for the following scenario:

Here, I have a slicer named "Fiscal Year" that contains data like 2019/2020, 2020/2021, etc., in text format. If I select 2023/24 from the slicer assuming as a current slicer, here the latest audit happened for Biscuits for Adequate is 2022/23 and now I want count od Adequate for previous Audit. if there is no previous audit then it should display na. how can write this using dax.
I have placed sample dataset for better understanding.
https://www.dropbox.com/scl/fi/5k46fdr2b2kwrkwyd1mwv/test.pbix?rlkey=gs3e020c91pj4mdwtt5kcsv8t&st=8b...


TIA

[Anonymous]

Hi @Sri57 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

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

[Sri57]

@Anonymous HERE IS THE DAX
 PreviousAudit =

VAR CurrentFiscalYear = CALCULATE(max('Audit'[FISCAL_YEAR_ID]),FILTER(Audit,Audit[RATING] = "Adequate"))

var LBOFY = MAXX(FILTER(Audit,Audit[FISCAL_YEAR_ID]<CurrentFiscalYear && Audit[RATING] = "Adequate"),Audit[FISCAL_YEAR_ID])

var result =

CALCULATE(COUNT(Audit[ENGAGEMENT_NAME]),Audit[FISCAL_YEAR_ID]=LBOFY,Audit[RATING]="Adequate")

RETURN IF(result=BLANK(),"na",result)

TIA

[lbendlin]

here the latest audit happened for Biscuits for Adequate is 2022/23 and now I want count od Adequate for previous Audit.

not clear to me what you are trying to achieve.

  Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.