cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

DAX help

Hi all,

I have one scenario where i have to find the maximum date and when there is no maximum date, it has to consider previous max date.

 Date Date2 Max date 12-01-2021 10-14-2021 12-16-2021 12-01-2021 10-15-2021 12-16-2021 12-01-2021 11-16-2021 12-16-2021 12-01-2021 12-15-2021 12-16-2021 12-01-2021 12-16-2021 12-16-2021 12-01-2021 02-15-2022 12-16-2021 01-01-2022 10-14-2021 12-15-2021 01-01-2022 10-15-2021 12-15-2021 01-01-2022 11-16-2021 12-15-2021 01-01-2022 12-15-2021 12-15-2021 01-01-2022 02-14-2022 12-15-2021 01-01-2022 02-15-2022 12-15-2021 02-01-2022 10-14-2021 02-15-2022 02-01-2022 10-15-2021 02-15-2022 02-01-2022 11-16-2021 02-15-2022 02-01-2022 12-15-2021 02-15-2022 02-01-2022 01-16-2022 02-15-2022 02-01-2022 02-15-2022 02-15-2022

I have two dates, Date and Date2. I will be using Date column as a filter and the max date value has to be calculated from Date 2.

So when i select December month from Date filter, the max date value should be the december month max date from Date 2.

When i select january and since there is no january date in Date 2 column, it has to consider previous max date which is december max date.

Then when February is selected, the max date should be feb month max date from Date 2 column.

Kindly help me with this. This can be achieved either in a measure or a calculated column.

1 ACCEPTED SOLUTION
Community Support

Hi @AishwariyaV ,

Test the measure like the below:

``````outputvalue =
var test=MAXX (
FILTER (
ALL ( Sheet1 ),
Sheet1[Date]= max(Sheet1[Date])
&& format(Sheet1[Date],"YYYYMM")>=format(Sheet1[Date2],"YYYYMM")
),
Sheet1[Date2]
)  return CALCULATE(MAX(Sheet1[Value]),Sheet1[Date2]=test)``````

Output:

Best Regards

Lucien

4 REPLIES 4
Community Support

Hi @AishwariyaV ,

Test the measure like the below:

``````outputvalue =
var test=MAXX (
FILTER (
ALL ( Sheet1 ),
Sheet1[Date]= max(Sheet1[Date])
&& format(Sheet1[Date],"YYYYMM")>=format(Sheet1[Date2],"YYYYMM")
),
Sheet1[Date2]
)  return CALCULATE(MAX(Sheet1[Value]),Sheet1[Date2]=test)``````

Output:

Best Regards

Lucien

Resolver II

Can you keep only date1 and date2 and provide sample required result in an excel and provide ?

Regards,

DI

Helper IV

Sample data :

 Date Date2 Value 12-01-2021 10-14-2021 12 12-01-2021 10-15-2021 34 12-01-2021 11-16-2021 38 12-01-2021 12-15-2021 63 12-01-2021 12-16-2021 13 12-01-2021 02-15-2022 98 01-01-2022 10-14-2021 34 01-01-2022 10-15-2021 65 01-01-2022 11-16-2021 77 01-01-2022 12-15-2021 23 01-01-2022 02-14-2022 45 01-01-2022 02-15-2022 60 02-01-2022 10-14-2021 20 02-01-2022 10-15-2021 39 02-01-2022 11-16-2021 88 02-01-2022 12-15-2021 43 02-01-2022 01-16-2022 90 02-01-2022 02-15-2022 54

Result :

 Month from Date column Value Dec21 13 Jan22 23 Feb22 54
Resolver II

MaxDate2withrespecttoDate =
VAR cDate = Sheet1[Date].[Date]
RETURN
MAXX (
FILTER (
ALL ( Sheet1 ),
Sheet1[Date].[Date] = cDate
&& Sheet1[Date2].[Date] < Sheet1[Date].[Date]
),
Sheet1[Date2].[Date]
)

Regards

DI

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors