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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AishwariyaV
Helper IV
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.

DateDate2Max date
12-01-202110-14-202112-16-2021
12-01-202110-15-202112-16-2021
12-01-202111-16-202112-16-2021
12-01-202112-15-202112-16-2021
12-01-202112-16-202112-16-2021
12-01-202102-15-202212-16-2021
01-01-202210-14-202112-15-2021
01-01-202210-15-202112-15-2021
01-01-202211-16-202112-15-2021
01-01-202212-15-202112-15-2021
01-01-202202-14-202212-15-2021
01-01-202202-15-202212-15-2021
02-01-202210-14-202102-15-2022
02-01-202210-15-202102-15-2022
02-01-202211-16-202102-15-2022
02-01-202212-15-202102-15-2022
02-01-202201-16-202202-15-2022
02-01-202202-15-202202-15-2022

AishwariyaV_0-1647329978791.png

 

 

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
v-luwang-msft
Community Support
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:

vluwangmsft_0-1647942106438.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
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:

vluwangmsft_0-1647942106438.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

darshaningale
Resolver II
Resolver II

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

Regards,

DI

@darshaningale ,

Sample data :

 

DateDate2Value
12-01-202110-14-202112
12-01-202110-15-202134
12-01-202111-16-202138
12-01-202112-15-202163
12-01-202112-16-202113
12-01-202102-15-202298
01-01-202210-14-202134
01-01-202210-15-202165
01-01-202211-16-202177
01-01-202212-15-202123
01-01-202202-14-202245
01-01-202202-15-202260
02-01-202210-14-202120
02-01-202210-15-202139
02-01-202211-16-202188
02-01-202212-15-202143
02-01-202201-16-202290
02-01-202202-15-202254

 

Result :

 

Month from Date columnValue
Dec2113
Jan2223
Feb2254

@AishwariyaV  Create below calculated column and add to the table. Hope you get your answer.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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