Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
passerf
Frequent Visitor

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

 

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.

passerf_0-1700507599095.png

 

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.

 

passerf_1-1700507757749.png

 

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 

3 REPLIES 3
passerf
Frequent Visitor

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

123abc
Community Champion
Community Champion

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.

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."

passerf_0-1700840274116.png

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors