Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help on a creating a measure formula

I have an excel spreadsheet (which I provided a little example of below) where it keeps track of all of the audits that we have performed since 2016. This excel spreadsheet shows the business name, project status, project type, report rating, and more.  Since this file goes back all the way to 2016, there are multiple times a business can be within the spreadsheet. I was able to come up with a formula to show the most recent year we have audited each business.  But now i want to show the most recent audit report rating.

 

Here is the meaure formula I created to show the most recent audit done (year) for each business:

Last Audit = calculate(MAX('Project Status'[Year]),FILTER('Project Status','Project Status'[Project Type] = "Full Scope"&&'Project Status'[Status]<>"Not Started"&&'Project Status'[Status]<>"Postponed"))
 
Below is the example data. As you can see there are many that dont have information as it may be outdated so i want my formula to pull the most recent Report Rating (Based on the year) or the report issue date). I want this to be a measure so i can use it in a visual.
YearBusiness UnitProject TypeReport Issue DateReport RatingStatus
2016PopFinancial  Complete
2016PaperFinancial  Complete
2016PaperIT  Complete
2016PopIT  Complete
2016PenFull Scope  Complete
2016JuiceFull Scope  Complete
2017PopFinancial  Complete
2017PaperFinancial  Complete
2017WhiskeyFull Scope  Complete
2017PaperFull Scope  Complete
2017WineFull Scope  Complete
2018BlanketFull Scope4/11/2018SatisfactoryComplete
2018BeerFull Scope5/15/2018SatisfactoryComplete
2018JuiceFull Scope6/21/2018SatisfactoryComplete
2018PopFull Scope8/9/2018SatisfactoryComplete
2018PrinterInvestigation5/21/2018N/AComplete
2018WaterFull Scope  Postponed
2018WineManagement Request  Postponed
2018WhiskeyFinancial Improvement NeededComplete
2018PopFinancial  Complete
2018PaperFinancial  Complete
2019WhiskeyFull Scope6/23/2019Improvement NeededComplete
2019PencilFull Scope4/26/2019Less Than SatisfactoryComplete
2019PrinterFull Scope2/11/2020Improvement NeededComplete
2019WaterFull Scope11/7/2019SatisfactoryComplete
2019WineSpecial Project  Postponed
2019PenFull Scope11/22/2019SatisfactoryComplete
2019BeerManagement Request9/10/2019SatisfactoryComplete
2019PaperFinancial N/AComplete
2019PopFinancial N/AComplete
2019PencilManagement Request N/AComplete
2019PopInvestigation N/AComplete
2020PencilManagement Request N/AFieldwork
2020PaperFull Scope  Planning
2020WineFull Scope  Postponed
2020PencilFull Scope  Not Started
2020JuiceFull Scope  Not Started
2020PaperFinancial  Not Started
2020PopFinancial  Not Started
2020PaperSpecial Project  Not Started
2020PenSpecial Project  Not Started
2020WineSpecial Project  Not Started
 
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Add the condition to the if statement.

    Measure =
VAR max_report_date =
    CALCULATE (
        MAX ( 'Table'[Report Issue Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
            && MAX ( 'Table'[Report Issue Date] ) = max_report_date
            && MAX ( 'Table'[Project Type] ) = "Full Scope",
        1,
        0
    )

    You can also refer to the Sample pbix

     

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

6 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please try the DAX below:

    Measure =
//Get the latest report date for each Business Unit
VAR max_report_date =
    CALCULATE (
        MAX ( 'Table'[Report Issue Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
    )
//Determine whether the date of the current line is the latest date
RETURN
    IF (
        NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
            && MAX ( 'Table'[Report Issue Date] ) = max_report_date,
        1,
        0
    )

    Sample .pbix

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This did not give me what i was looking for.  Below is what the solution should look like. It would show the Report Rating of the most recent Full Scope (Project Type) audit. If a businuss unit does not have a Report Rating of a full scope auduit it should be left blank.

       

      YearBusiness UnitProject TypeReport Issue DateReport RatingStatus
      2018BeerFull Scope5/15/2018SatisfactoryComplete
      2018BlanketFull Scope4/11/2018SatisfactoryComplete
      2018JuiceFull Scope6/21/2018SatisfactoryComplete
      2019PenFull Scope11/22/2019SatisfactoryComplete
      2019PencilFull Scope4/26/2019Less Than SatisfactoryComplete
      2018PopFull Scope8/9/2018SatisfactoryComplete
      2019PrinterFull Scope2/11/2020Improvement NeededComplete
      2019WaterFull Scope11/7/2019SatisfactoryComplete
      2019WhiskeyFull Scope6/23/2019Improvement NeededComplete
      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Add the condition to the if statement.

        Measure =
VAR max_report_date =
    CALCULATE (
        MAX ( 'Table'[Report Issue Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Business Unit] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( MAX ( 'Table'[Report Issue Date] ) ) )
            && MAX ( 'Table'[Report Issue Date] ) = max_report_date
            && MAX ( 'Table'[Project Type] ) = "Full Scope",
        1,
        0
    )

        You can also refer to the Sample pbix

         

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    On the Data that you have shared, please show the exact expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Below is what the solution should look like. It would show the Report Rating of the most recent Full Scope (Project Type) audit. If a businuss unit does not have a Report Rating of a full scope auduit it should be left blank.

       

      YearBusiness UnitProject TypeReport Issue DateReport RatingStatus
      2018BeerFull Scope5/15/2018SatisfactoryComplete
      2018BlanketFull Scope4/11/2018SatisfactoryComplete
      2018JuiceFull Scope6/21/2018SatisfactoryComplete
      2019PenFull Scope11/22/2019SatisfactoryComplete
      2019PencilFull Scope4/26/2019Less Than SatisfactoryComplete
      2018PopFull Scope8/9/2018SatisfactoryComplete
      2019PrinterFull Scope2/11/2020Improvement NeededComplete
      2019WaterFull Scope11/7/2019SatisfactoryComplete
      2019WhiskeyFull Scope6/23/2019Improvement NeededComplete
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

        If i filter the Project type column on Full Scope and the Report issue data on non blanks, i am left with your exact expected result without writing any formula at all.  This is because after applying these 2 filter conditions, only one row combination of Year and Business Unit is left.  So why do we need to write any formula at all.  Just apply filters in the visual as i have mentioend.  Here's a screenshot