DAX Calculated Column for PRE/POST Sequence in Power BI

Adnaniqb — Sun, 20 Oct 2024 12:18:11 GMT

I'm working with a Power BI model that includes a table called "All Depots". This is a large, granular table that includes details by customer and product. However, for this particular problem, I'm focusing only on the "Depot", "PRE/POST", and "Transaction date" fields.

## Objective
I need to create a calculated column that assigns a sequence number to PRE and POST periods for each depot. The sequence should work as follows:

- For PRE periods:
- The last (most recent) PRE date should be assigned -1
- The second-to-last PRE date should be assigned -2
- The third-to-last PRE date should be assigned -3
- And so on...

- For POST periods:
- The first (earliest) POST date should be assigned +1
- The second POST date should be assigned +2
- The third POST date should be assigned +3
- And so on...

- The sequence should reset for each depot

## Current Approach
I've tried several DAX formulas, including variations of RANKX and COUNTROWS, but I haven't been able to achieve the desired result. Here's an example of one approach I've tried:

PRE_POST_Sequence = VAR CurrentDepot = 'All Depots'[Depot] VAR CurrentDate = 'All Depots'[Transaction date] VAR CurrentPREPOST = 'All Depots'[PRE/POST] VAR PRE_Sequence = IF(CurrentPREPOST = "PRE", -RANKX( FILTER(ALL('All Depots'), 'All Depots'[Depot] = CurrentDepot && 'All Depots'[PRE/POST] = "PRE" ), 'All Depots'[Transaction date], , DESC ), BLANK() ) VAR POST_Sequence = IF(CurrentPREPOST = "POST", RANKX( FILTER(ALL('All Depots'), 'All Depots'[Depot] = CurrentDepot && 'All Depots'[PRE/POST] = "POST" ), 'All Depots'[Transaction date], , ASC ), BLANK() ) RETURN IF(ISBLANK(PRE_Sequence), POST_Sequence, PRE_Sequence)

```

## Problem
The current formula isn't producing the expected results. It's either not sequencing correctly or not resetting for each depot as needed.

## Question
Can anyone suggest a DAX formula that would achieve the desired sequencing as described above? I'm open to completely different approaches if they can solve this problem more effectively.

## Additional Information
- The "All Depots" table contains multiple rows per day per depot.
- The "PRE/POST" field contains only "PRE" or "POST" values.
- The "Transaction date" is a date field.

Thank you in advance for any help or suggestions!

Re: DAX Calculated Column for PRE/POST Sequence in Power BI

Jihwan_Kim — Sun, 20 Oct 2024 13:17:13 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

RANK function (DAX) - DAX | Microsoft Learn

PRE_POST_Sequence = VAR _PRESequenceTable = FILTER ( SUMMARIZE ( 'ALL Depots', 'ALL Depots'[Depot], 'ALL Depots'[Transaction date], 'ALL Depots'[PRE/POST] ), 'ALL Depots'[PRE/POST] = "PRE" ) VAR _POSTSequenceTable = FILTER ( SUMMARIZE ( 'ALL Depots', 'ALL Depots'[Depot], 'ALL Depots'[Transaction date], 'ALL Depots'[PRE/POST] ), 'ALL Depots'[PRE/POST] = "POST" ) VAR _PRERank = RANK ( SKIP, _PRESequenceTable, ORDERBY ( 'ALL Depots'[Transaction date], DESC ), , PARTITIONBY ( 'ALL Depots'[Depot] ), MATCHBY ( 'ALL Depots'[Depot], 'ALL Depots'[Transaction date], 'ALL Depots'[PRE/POST] ) ) * -1 VAR _POSTRank = RANK ( SKIP, _POSTSequenceTable, ORDERBY ( 'ALL Depots'[Transaction date], ASC ), , PARTITIONBY ( 'ALL Depots'[Depot] ), MATCHBY ( 'ALL Depots'[Depot], 'ALL Depots'[Transaction date], 'ALL Depots'[PRE/POST] ) ) RETURN _PRERank + _POSTRank

topic Re: DAX Calculated Column for PRE/POST Sequence in Power BI in DAX Commands and Tips

DAX Calculated Column for PRE/POST Sequence in Power BI

Re: DAX Calculated Column for PRE/POST Sequence in Power BI