Forum Discussion

AishwariyaV's avatar
AishwariyaV
Helper IV
4 years ago
Solved

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-...
Need Help
Show and Tell
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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:

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


    Best Regards

    Lucien

darshaningale's avatar
darshaningale
Resolver II
4 years ago

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

Regards,

DI

AishwariyaV's avatar
AishwariyaV
Helper IV
4 years ago

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
  • darshaningale's avatar
    darshaningale
    Resolver II
    4 years ago

    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