Forum Discussion

Sri57's avatar
Sri57
Frequent Visitor
2 years ago

DAX Query

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

@help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Sri57
      Frequent Visitor
      2 years ago
      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