DAX measure SUM with filter on prior period

[tcash]

I have a table with quarterly snapshots containing the customer number, end of quarter date, and 'level of engagement' columns.   The 'level of engagement' column has a whole number datatype.  That column has value of zero or one.  Once the 'level of engagement' is set to one, the customer retains that engagement level for the remaining quarters of the year.  A value of one indicates the customer is highly engaged. Note that I also have a Date Table related to the Quarterly Date.

My objective is to generate a measure with a count of the 'highly engaged' customers over time without counting the same customer as highly engaged more than once over multiple quarters.   That is, if the customer is highly engaged in Q1 then the customer remains highly engaged for each Q2, Q3, and Q4.   However, I only want to count this customer as highly engaged once for the entire year and not as highly engaged 4 times.   Similarly, if the customer has a zero 'level of engagement' in Q1 and a 1 in Q2, then it should count as 0 in Q1 and should only count once for the rest of the year. 

What sort of a DAX pattern would such a measure have?

[Ashish_Mathur]

Hi,

Share the link from where i can download your workbook and show the expected result.

[tcash]

PBIXexampleOfQuarterlySnapshotScenarios   This example has my 3 scenarios and includes the calculated column from which I can summarize. 

As an inexperienced DAX coder it took me a day to better absorb the Row Context concept and its role with the EARLIER function that I used in this calculation.  I realize the column calculation will be iterated a few hundred thousand times in my real world scenario, so please let me know if there is a better performing solution.   In any event, my early testing is showing it performs OK enough.

[Ashish_Mathur]

Hi,

Please correct my understandin below:

1. If a customer is highly engaged in Q1, then he will remain so for the other quarters also even though for that customer, in other quarters the value in the "Is New Highly engaged" column is FALSE
2. If a customer is not highly engaged in Q1 but highly engaged in Q2, then he will have a value of 0 for Q1 and 1 for other quarters even though for that customer, in other quarters the value in the "Is New Highly engaged" column is FALSE

I am just not clear about your rules.  Please take a few cases to show/explain the nuances with reasoning.

[tcash]

Hi,

Thanks for taking the time to consider this use case.   Here are my scenarios as depticted by the data in the sample LevelOfEngagement table:

1. Email Address 01@test.com: IsNewHighlyEngaged = FALSE for Q1 and Q2.   Expected Outcome-->it should never count in the SUM of newly engaged Contacts.
2. Email Address 3g@test.com: IsNewHighlyEngaged = TRUE in Q1 and Q2.   Expected Outcome-->, it should count only 1x in the Sum of newly engaged Contacts.
3. Email Address 86@test.com: IsNewHighlyEngaged = FALSE in Q1 and TRUE in Q2.  Although I did not provide test data for Q3 and Q4, you may assume the IsNewHighlyEngaged would remain TRUE for Q3 and Q4.  Expected Outcome-->, it should count only 1x in the Sum of newly engaged Contacts for Q2, Q3, and Q4.  It should not count in the SUM for Q1.

The Page 1 tab of the Report in the sample PBIX shows the expected result.  As I said earlier, I ended up creating the calculated column called HighlyEngagedCount that serves the purpose.  At this point I am simply curious whether using a calculated column the iterates through the table for each row is the best solution.

Thanks!

[Ashish_Mathur]

Hi,

Thank you for clarifying.  Since your question relates to "efficeincy of your working solution",  I'd let someone else who is knowledgeable on the issue answer this question.  All i know is that a measure is more often that not more efficeicy than a calculated column.

[tcash · Accepted solution]

May not be the most performant and I need to do some testing...but adding this calculated column seems to work when I SUM it up as a measure over time:

HighlyEngagedCount=
VAR    
NewHighly = IF('LevelOfEngagement'[Is New Highly Engaged]=TRUE(),1,0)
RETURN
IF(
    CALCULATE(COUNTROWS(LevelOfEngagement),
        FILTER('LevelOfEngagement','LevelOfEngagement'[Email Address]=EARLIER('LevelOfEngagement'[Email Address])),
        FILTER('LevelOfEngagement','LevelOfEngagement'[SnapshotDate]<EARLIER('LevelOfEngagement'[SnapshotDate])),
        FILTER('LevelOfEngagement','LevelOfEngagement'[Is New Highly Engaged]=TRUE))
            = 0,NewHighly,0)