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