DAX Calculated Column for PRE/POST Sequence in Power BI
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!
