topic Re: DAX Query in DAX Commands and Tips

DAX Query

Sri57 — Thu, 27 Jun 2024 13:45:46 GMT

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=8b7ml66c&dl=0

TIA

Re: DAX Query

lbendlin — Fri, 28 Jun 2024 00:54:31 GMT

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.

Re: DAX Query

Anonymous — Fri, 05 Jul 2024 06:35:21 GMT

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.

Re: DAX Query

Sri57 — Fri, 05 Jul 2024 15:09:42 GMT

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