topic Re: Returning a separate planned date field based off the associated MAXX trend date in the same row in DAX Commands and Tips

Returning a separate planned date field based off the associated MAXX trend date in the same row

passerf — Mon, 27 Nov 2023 22:52:22 GMT

Hello Power BI Community,

This may be a simple fix, but let me elaborate on the challenge encountered and what I am trying to achieve.

I have searched other Max Date problem statements, but haven't found an exact use case similar to my desired intent.

Background: I am creating a conditional table based off multiple different date values, but most importantly the max pull of a Trend Date. I have data in one Table called 'Sample Data', I have 5 concatenated data values in a calculated column - Product, Customer, Type, Phase, and Status. Here is a Sample Data PBIX File to visualize what I am trying to achieve with the desired DAX formula.

DAX Intent: I am trying to populate both the Planned & Requested Dates (which can be located in the 'Drill-Through Metadata' page when selected) of the associated "MAXX Trend Date" data row.

All rows have multiple Trend Dates, the MAXX Trend Date is correctly returning the max trend date for the concatenated fields in the logic.

Here are the steps of my current progress:

Step 1. I created a calculated column called "Concatenated Fields" using the Product, Customer, Type, Phase, & Status fields from the Sample Data table.

DAX

Concatenated Fields = CONCATENATE('Sample Data'[Product],

CONCATENATE('Sample Data'[Customer],

CONCATENATE('Sample Data'[Type],

CONCATENATE('Sample Data'[Phase],'Sample Data'[Status]))))

Step 2. I created a measure called "MAXX Trend Date" involving the "Concatenated Fields" column to show the long pole of each max Trend Date of the unique rows separated into the 3 phase pages - First Phase, Second Phase, Third Phase.

DAX

MAXX Trend Date = Var _Datavalues = SELECTEDVALUE('Sample Data'[Concatenated Fields])

Return

MAXX(FILTER(ALL('Sample Data'),'Sample Data'[Concatenated Fields] = _Datavalues),'Sample Data'[Trend Date])

Step 3. I created another calculated column called "Concated Fields with MAXX Trend Date" to use the unique rows of the MAXX Trend Date and attempt to return the associated Planned & Requested Dates of the MAXX Trend Date

DAX

Concatenated Fields with MAXX Trend Date = CONCATENATE('Sample Data'[MAXX Trend Date], [Concatenated Fields])

Step 4. [DAX HELP REQUESTED] In this step I am trying to find the correct DAX logic to return the selected value of each of the Planned and Request Dates that are associated to the MAXX Trend Date value.

INCORRECT DAX

INCORRECT DAX - Selected Value Planned Date = Var _SelectedValuePlannedDate = SELECTEDVALUE('Sample Data'[Concatenated Fields with MAXX Trend Date])

Return

MAXX(FILTER(ALL('Sample Data'),'Sample Data'[Concatenated Fields with MAXX Trend Date] = _SelectedValuePlannedDate), 'Sample Data'[Planned Date])

I understand in Step 4 the DAX is incorrect because the formula does not return the correct Planned Date value as all values are blank.

Example Screenshots

As I select the first row in the "First Phase" page, the MAXX Trend Date = 2023-12-27, which is correctly represented for the concatenated criteria.

As I Drill Through the selected first row to the metadata, I see multiple rows containing dates, but the MAXX Trend Date logic is correctly returning the max Trend Date value of 2023-12-27.

I want to find the DAX logic to also return the associated Requested (2023-11-10) and Planned (2023-11-11) Dates of the max Trend Date value, to include those calculated columns on the "First Phase" report page.

Any DAX assistance with the desired objective would be greatly appreciated! Please advise if I have not articulated the DAX issue well enough and I will further elaborate, thank you in advance. Sample Data PBIX File

Re: Returning a separate planned date field based off the associated MAXX trend date in the same row

123abc — Tue, 21 Nov 2023 05:42:48 GMT

It looks like you're trying to retrieve the Planned and Requested Dates associated with the MAX Trend Date for each row in your data. The key here is to use the MAXX Trend Date as a filter to identify the corresponding Planned and Requested Dates. Below is the corrected DAX for your Step 4:

Selected Value Planned Date =
VAR _SelectedValuePlannedDate = SELECTEDVALUE('Sample Data'[MAXX Trend Date])
RETURN
CALCULATE(
MAX('Sample Data'[Planned Date]),
'Sample Data'[MAXX Trend Date] = _SelectedValuePlannedDate
)

Similarly, you can create a measure for the Requested Date:

Selected Value Requested Date =
VAR _SelectedValuePlannedDate = SELECTEDVALUE('Sample Data'[MAXX Trend Date])
RETURN
CALCULATE(
MAX('Sample Data'[Requested Date]),
'Sample Data'[MAXX Trend Date] = _SelectedValuePlannedDate
)

This DAX code uses the CALCULATE function to apply a filter on the 'Sample Data' table, specifying that the 'MAXX Trend Date' should be equal to the selected MAX Trend Date. This way, you get the Planned and Requested Dates corresponding to the MAX Trend Date.

Make sure to replace 'Requested Date' and 'Planned Date' with your actual column names if they are different in your dataset.

Remember to also consider scenarios where there might be multiple rows with the same MAX Trend Date. In such cases, the MAX function used in the 'MAXX Trend Date' measure will return a single value, but you should still make sure that the logic fits your specific use case.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Returning a separate planned date field based off the associated MAXX trend date in the same row

passerf — Fri, 24 Nov 2023 16:00:55 GMT

Hi 123abc,

Thank you for your time responding to my DAX problem. I received the error in the image below when inputting your recommended DAX formula.
"Column 'MAXX Trend Date' in table 'Sample Data' cannot be found or may not be used in this expression."

I tried creating both a measure and a column with the DAX formula and have received errors.
I have been exploring numerous combinations of SELECTEDVALUE, CALCULATE, & FILTER functions to retrieve the correct associated Planned & Requested End Dates relative to the MAXX Trend Date in the same data row, but have failed to successfully to do so.

Any further DAX assistance on this topic would be greatly appreciated, thanks again for the effort!

Re: Returning a separate planned date field based off the associated MAXX trend date in the same row

passerf — Mon, 27 Nov 2023 22:53:04 GMT

Any other DAX function recommendations to achieve the desired date value result? Thanks in advance.