average of a measure, 30 days from min start date (not aggregate) Urgent help needed!

[Anonymous]

Hi. I have a data table that looks like this where a new row is created every time the user updated their happiness level:

UserID	Happiness Level	DateUpdated
1	5	12/17/19
2	6	12/17/19
3	3	12/17/19
4	4	12/18/19
5	8	12/18/19
1	7	12/20/19
1	7	1/16/20
2	2	1/16/20
3	3	1/16/20
3	9	1/18/20
4	7	1/17/20
5	9	1/17/20

I want to average the happiness level of a cohort on the 30th day AFTER their first(MIN) report (so, cohort of USER IDs 1, 2, and 3 average  on 1/16/20 and user ID 4 and 5 on 1/17/20.) Only the 30th day after each cohort so I wind up with a table like this:

start date	first happiness avg	30 days later happiness acg
1/17/19	#	#
1/18/19	#	#

I used this to get the initial (MIN) avg happiness level, but cannot figure out how to calculate the same thing 30 days after the MIN. Can you please help? 

OTP2 =
SUMX (
    DISTINCT ( DelOrder[OrderLineID] );
    VAR firstArrivalDate =
        CALCULATE ( MIN ( DelOrder[Orderline Arrival Date] ) )
    RETURN
        CALCULATE (
            SUM ( DelOrder[On Time] );
            FILTER ( DelOrder; DelOrder[Orderline Arrival Date] = firstArrivalDate )
        )
)
    / DISTINCTCOUNT ( DelOrder[OrderLineID] )

[sturlaws]

Hi @Anonymous 

based on the data sample you have provided, I got these two measures to work:

Average happiness first entry =
VAR _firstEntry =
    ADDCOLUMNS (
        VALUES ( happiness[UserID] );
        "f";
        VAR _currentUserId =
            CALCULATE ( SELECTEDVALUE ( happiness[UserID] ) )
        RETURN
            CALCULATE (
                MIN ( happiness[DateUpdated] );
                FILTER ( ALL ( happiness ); happiness[UserID] = _currentUserId )
            )
    )
VAR _aux =
    SUMMARIZE ( happiness; happiness[UserID]; happiness[DateUpdated] )
RETURN
    AVERAGEX (
        FILTER ( happiness; COUNTROWS ( INTERSECT ( _aux; _firstEntry ) ) > 0 );
        AVERAGE ( happiness[Happiness Level] )
    )

and 

Average happiness 30 days later = 
VAR _30dayslater =
    ADDCOLUMNS (
        VALUES ( happiness[UserID] );
        "30dayslater";
        VAR _currentUserId =
            CALCULATE ( SELECTEDVALUE ( happiness[UserID] ) )
        RETURN
            CALCULATE (
                MIN ( happiness[DateUpdated] ) + 30;
                FILTER ( ALL ( happiness ); happiness[UserID] = _currentUserId )
            )
    )
VAR _aux =
    ADDCOLUMNS (
        VALUES ( happiness[UserID] );
        "du"; CALCULATE ( SELECTEDVALUE ( happiness[DateUpdated] ) + 30 )
    )
RETURN
    CALCULATE (
        AVERAGE ( happiness[Happiness Level] );
        VAR _cd =
            CALCULATE ( SELECTEDVALUE ( happiness[DateUpdated] ) ) + 30
        RETURN
            FILTER (
                ALL ( happiness );
                COUNTROWS ( INTERSECT ( _aux; _30dayslater ) ) > 0
                    && happiness[DateUpdated] = _cd
            )
    )

Output:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Thanks for this - can you send me your Pbix file? For whatever reason, this is working but then when I put it in a matrix view, it only works on daily form, not by "start of month" or "start of week" trying to figure that out 

[sturlaws]

The code must be modified if that is the behaviour you want. But also have to define the behaviour of what should happen if user changes happiness within a month/week. What if there are multiple changes within the chosen period?

As for the pbix, it is just the data you provided and the dax code for measure

[Anonymous]

Can you help me modify accordingly then to show monthly and to show 1 number average everyone reports after 30 days? This is a critical number for my dashboard and would definitely appreciate your help! 

[sturlaws]

So what you want is, if more than 1 entry in a month, average the values?

It's doable as a measure, but it is much easier with a few extra columns:
sample report