Help on a creating a measure formula

[Anonymous]

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.

Year	Business Unit	Project Type	Report Issue Date	Report Rating	Status
2016	Pop	Financial			Complete
2016	Paper	Financial			Complete
2016	Paper	IT			Complete
2016	Pop	IT			Complete
2016	Pen	Full Scope			Complete
2016	Juice	Full Scope			Complete
2017	Pop	Financial			Complete
2017	Paper	Financial			Complete
2017	Whiskey	Full Scope			Complete
2017	Paper	Full Scope			Complete
2017	Wine	Full Scope			Complete
2018	Blanket	Full Scope	4/11/2018	Satisfactory	Complete
2018	Beer	Full Scope	5/15/2018	Satisfactory	Complete
2018	Juice	Full Scope	6/21/2018	Satisfactory	Complete
2018	Pop	Full Scope	8/9/2018	Satisfactory	Complete
2018	Printer	Investigation	5/21/2018	N/A	Complete
2018	Water	Full Scope			Postponed
2018	Wine	Management Request			Postponed
2018	Whiskey	Financial		Improvement Needed	Complete
2018	Pop	Financial			Complete
2018	Paper	Financial			Complete
2019	Whiskey	Full Scope	6/23/2019	Improvement Needed	Complete
2019	Pencil	Full Scope	4/26/2019	Less Than Satisfactory	Complete
2019	Printer	Full Scope	2/11/2020	Improvement Needed	Complete
2019	Water	Full Scope	11/7/2019	Satisfactory	Complete
2019	Wine	Special Project			Postponed
2019	Pen	Full Scope	11/22/2019	Satisfactory	Complete
2019	Beer	Management Request	9/10/2019	Satisfactory	Complete
2019	Paper	Financial		N/A	Complete
2019	Pop	Financial		N/A	Complete
2019	Pencil	Management Request		N/A	Complete
2019	Pop	Investigation		N/A	Complete
2020	Pencil	Management Request		N/A	Fieldwork
2020	Paper	Full Scope			Planning
2020	Wine	Full Scope			Postponed
2020	Pencil	Full Scope			Not Started
2020	Juice	Full Scope			Not Started
2020	Paper	Financial			Not Started
2020	Pop	Financial			Not Started
2020	Paper	Special Project			Not Started
2020	Pen	Special Project			Not Started
2020	Wine	Special Project			Not Started

[Ashish_Mathur]

Hi,

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

[Anonymous]

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.

Year	Business Unit	Project Type	Report Issue Date	Report Rating	Status
2018	Beer	Full Scope	5/15/2018	Satisfactory	Complete
2018	Blanket	Full Scope	4/11/2018	Satisfactory	Complete
2018	Juice	Full Scope	6/21/2018	Satisfactory	Complete
2019	Pen	Full Scope	11/22/2019	Satisfactory	Complete
2019	Pencil	Full Scope	4/26/2019	Less Than Satisfactory	Complete
2018	Pop	Full Scope	8/9/2018	Satisfactory	Complete
2019	Printer	Full Scope	2/11/2020	Improvement Needed	Complete
2019	Water	Full Scope	11/7/2019	Satisfactory	Complete
2019	Whiskey	Full Scope	6/23/2019	Improvement Needed	Complete

[Ashish_Mathur]

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

[V-lianl-msft]

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]

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.

Year	Business Unit	Project Type	Report Issue Date	Report Rating	Status
2018	Beer	Full Scope	5/15/2018	Satisfactory	Complete
2018	Blanket	Full Scope	4/11/2018	Satisfactory	Complete
2018	Juice	Full Scope	6/21/2018	Satisfactory	Complete
2019	Pen	Full Scope	11/22/2019	Satisfactory	Complete
2019	Pencil	Full Scope	4/26/2019	Less Than Satisfactory	Complete
2018	Pop	Full Scope	8/9/2018	Satisfactory	Complete
2019	Printer	Full Scope	2/11/2020	Improvement Needed	Complete
2019	Water	Full Scope	11/7/2019	Satisfactory	Complete
2019	Whiskey	Full Scope	6/23/2019	Improvement Needed	Complete

[V-lianl-msft · Accepted solution]

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.