Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Sri57
Frequent Visitor

DAX Query

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

Sri57_1-1719493079717.png

 

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

3 REPLIES 3
Anonymous
Not applicable

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.

@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
Super User
Super User

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.

lbendlin_0-1719536068179.png

 

 

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

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.