Creating a new column that shows the running total based on multiple conditions

[CGodfrey]

Hey all,

I am looking to create a new column that will calculate the running total per unique identifier with multiple conditions.

Context: The table I am using is of Gift Card (GC) activations and redemptions. A new row is created each time a new action is logged. All unique GCs have one "Register" but can have multiple actions ( "Increment", "Redeem", "Forced Redeem").

Each time a unique card is redeemed ("Redeem" || "Forced Redeem") I want the running total of the number of redemptions to increase.

For this sample, I have created four scenarios: 

Scenario 1) Serial # 1234 -- Two redemptions on different days

Scenario 2) Serial # 5678 -- Three redemptions on different days

Scenario 3) Serial # 9123 -- Two redemptions on the same day, different times

Scenario 4) Serial # 4567 -- No redemption

Desired result in shown in the last column of the table:

Serial Number	Action	Action Date	Action Time	Register Date	Desired Result: Count of Redeem/Forced redeems
1234	Register	12-May-21	12:00:00 AM	12-May-21
1234	Redeem	14-May-21	1:00:00 AM	12-May-21	1
1234	Redeem	16-May-21	2:00:00 AM	12-May-21	2
5678	Register	1-May-21	12:00:00 AM	1-May-21
5678	Increment	2-May-21	4:00:00 AM	1-May-21
5678	Redeem	3-May-21	5:00:00 AM	1-May-21	1
5678	Forced Redeem	4-May-21	6:00:00 AM	1-May-21	2
5678	Forced Redeem	5-May-21	7:00:00 AM	1-May-21	3
9123	Register	1-Jan-21	8:00:00 AM	1-Jan-21
9123	Forced Redeem	1-Jan-21	9:00:00 AM	1-Jan-21	1
9123	Forced Redeem	1-Jan-21	10:00:00 AM	1-Jan-21	2
4567	Register	1-Feb-21	8:00:00 AM	1-Feb-21

I am really trying to learn how DAX is working so I would very much appreciate an explanation of the logic behind the proposed solution. I also wonder about the difference between doing this step in Power Query vs the report. What is the best practice that in terms of performance?

Thank you very much for your suggestions!

C

[parry2k · Accepted solution]

@CGodfrey you can also use RANK function, try following two columns:

Action Date Time = Action Date Time = GC[Action Date] + GC[Action Time]

Rank = 
IF ( 
    GC[Is Redeemed] = "Yes",
    RANKX ( 
        FILTER ( 
            ALL ( GC[Serial Number], GC[Action Date Time], GC[Action]  ), 
            GC[Serial Number] = EARLIER ( GC[Serial Number] )  &&  
            SEARCH ( "Redeem", GC[Action], , -1 ) <> -1 
        ),
        GC[Action Date Time] 
        , , ASC 
    )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[CGodfrey]

Hey @parry2k This solution has worked! I am struggling to understand what precisely is happening but good opportunity to wrap my head around RANKX. Thank you for your assistance!

[Greg_Deckler]

@CGodfrey Try:

Column = 
  VAR __SerialNumber = [Serial Number]
  VAR __Date = [Action Date]
RETURN
  COUNTROWS(FILTER('Table',[Serial Number] = __SerialNumber && [Action Date] <= __Date && [Action] = "Redeem"))

[CGodfrey]

@Greg_Deckler That certainly worked for the "Redeem" action. How could I make it to evaluate for both "Redeem" OR "Foreced Redeem"?

Also, in Scenario 3 above the GC is redeemed twice in the same day so it would have to then look at the Action Time to know which came first that day. 

[Greg_Deckler]

@CGodfrey Well the first one would be:

Column = 
  VAR __SerialNumber = [Serial Number]
  VAR __Date = [Action Date]
RETURN
  COUNTROWS(FILTER('Table',[Serial Number] = __SerialNumber && [Action Date] <= __Date && ([Action] = "Redeem") || [Action] = "Forced Redeem"))

For the second one, I would recommend creating a new calculated column in Power Query that combines your Date and Time into a single Datetime column and use that instead of [Date] in your formula.

[CGodfrey]

@Greg_Deckler I switched to the date/time combo which resolved some of the errornous results; however, it still evaluates actions that are not equal to "Redeem" or "Forced redeem" and assigns them an additonal value. More specifically, when the action = "Increment" which is very off as I'd expect that would be filtered out with the 

&& ([Action] = "Redeem") || [Action] = "Forced Redeem"))