Previous Status Measure

[thinker_02]

The "Profit" table has columns Product ID, Date, and Status, with Status categorized as High, Low, or Medium.

Snapshot of the Profit table:

| Product ID | Date | Status |
|------------|------------|--------|
| ID1 | 4/4/2022 | Low |
| ID2 | 8/1/2022 | High |
| ID1 | 10/12/2022 | High |
| ID1 | 2/1/2022 | Medium |
| ID2 | 1/1/2023 | Low |
| ID2 | 6/7/2022 | Medium |
| ID1 | 7/7/2023 | Medium |

Three measures were created for analysis:

 Min Date: 6/12/2022 and Max Date: 4/2/2023 (I selected them in Date Slicer too)
1.Latest Date: Checks date per Stock ID within the min and max dates.
2.Second Date: Checks date per Stock ID less than the min date, i.e., any entry just before the Latest Date.
3.Status Latest: Shows the Status per ID on the Latest Date.

Looking for help to create a measure for **Status Previous** to display the Status per ID on the Second Date.

Resulting table:

| Stock ID | Latest Date | Second Date | Status Latest | (Expected)Status Previous |
|----------|-------------|-------------|---------------|------------------|
| ID1 | 10/12/2022 | 4/4/2022 | High | Low |
| ID2 | 1/1/2023 | 6/7/2022 | Low | Medium |

Status Previous measure is intended to reveal the status of each ID on the second most recent date in the specified date range.

For the measures:

DAX Measures-

LatestDate = MAX('Profit'[Date])

SecondDate =
VAR MinDate = DATE(2022, 12, 6)
VAR MaxDate = DATE(2023, 2, 4)
VAR LatestDate = MAX('Profit'[Date])
RETURN
IF (
AND(
LatestDate >= MinDate,
LatestDate <= MaxDate
),
CALCULATE(
MAX('Profit'[Status]),
'Profit'[Date] < LatestDate
),
BLANK()
)

Latest Status =
CALCULATE(
MAX('Profit'[Status]),
FILTER(
'Profit',
'Profit'[Date] = [LatestDate]
)
)

However, when creating the Status Previous measure, it returns blank values. Looking forward. Thanks in advance!!

[v-yangliu-msft · Accepted solution]

Hi @thinker_02  ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _date=
MAXX(
   FILTER(ALLSELECTED('Table'),'Table'[Product ID]=MAX('Table'[Product ID])),[Date])
return
IF(
    MAX('Table'[Date])=_date,1,0)

Second Date =
 var _date=
MAXX(
    FILTER(ALLSELECTED('Table'),'Table'[Product ID]=MAX('Table'[Product ID])),[Date])
return
MAXX(
    FILTER(ALL('Table'),
    'Table'[Product ID]=MAX('Table'[Product ID])&&'Table'[Date]<_date),[Date])

Status Latest =
var _date=
MAXX(
    FILTER(ALLSELECTED('Table'),'Table'[Product ID]=MAX('Table'[Product ID])),[Date])
var _maxdate=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Product ID]=MAX('Table'[Product ID])&&'Table'[Date]<_date),[Date])
return
MAXX(
    FILTER(ALL('Table'),
   'Table'[Product ID]=MAX('Table'[Product ID])&& 'Table'[Date] =_maxdate),[Status])

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

PBI file attached.