My data includes customers' statuses over months. The aim is to add a column with a next month statuses using DAX.
| Month | CustomerID | Status |
|---|---|---|
| Jan | D1200 | Active |
| Jan | D1300 | Sleeping |
| Feb | D1200 | Sleeping |
| Feb | D1300 | Sleeping |
| Month | CustomerID | Status | Next Month Status |
|---|---|---|---|
| Jan | D1200 | Active | Sleeping |
| Jan | D1300 | Sleeping | Sleeping |
| Feb | D1200 | Sleeping | |
| Feb | D1300 | Sleeping |
I tried to LOOKUP a status while using Nextmonth() filter, but didn't get the result.
I've modified slightly your table to show the Month as a date format and also added two columns to demonstrate it works with different months.
| Month | CustomerID | Status |
|---|---|---|
| 01/01/2021 | D1200 | Active |
| 01/01/2021 | D1300 | Sleeping |
| 01/02/2021 | D1200 | Sleeping |
| 01/02/2021 | D1300 | Sleeping |
| 01/03/2021 | D1200 | Active |
| 01/03/2021 | D1300 | Sleeping |
Next Month Status =
VAR SelectedCustomerID = [CustomerID]
VAR SelectedMonth = [Month]
VAR NxMonth =
MINX ( FILTER ( 'Table', [Month] > SelectedMonth ), [Month] )
VAR StatusTable =
FILTER ( 'Table', [CustomerID] = SelectedCustomerID && [Month] = NxMonth )
RETURN
SUMMARIZE ( StatusTable, [Status] )
