CODEWITHSUNDEEP
powerbidax
Jo_Saga
Jo_Saga

Reputation: 35

Next month status in power bi

My data includes customers' statuses over months. The aim is to add a column with a next month statuses using DAX.

Original table:

Month CustomerID Status
Jan D1200 Active
Jan D1300 Sleeping
Feb D1200 Sleeping
Feb D1300 Sleeping

Target table:

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.

Upvotes: 0

Views: 175

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1791

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.

Table

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

Calculated Column

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] )

Output

enter image description here

Upvotes: 1

Related Questions