First/Last Values - Cumulative Total not working

[DanielB]

Hi All,

I am using a dataset similar to the following:

Person	Shop Visit	Time Spent
A	1	20
A	2	30
A	3	40
B	1	5
B	2	10
B	3	20
B	4	30
C	1	60

I am trying to obtain the Time Spent in the Last/First Visit by each person and a cumulative total for all 3, by using a measure (don't want columns as I need to insert different slicers which won't work if my formulas are on columns)

What I have tried:

Last Visit = CALCULATE(MAX([Shop Visit]),[Person])

Time Spent = CALCULATE(SUM([Time Spent]),FILTER(Table,MAX([Shop Visit])=[Last Visit]),[Person])

This works at 'Person Level' (i.e. it tells me for each person what was the time spent in the last visit) however as a Cumulative total it basically takes the person with the max amount of visits (or if I would have more persons with 4 visits in the case above, it would sum up the time spent only for those). I believe the way to go about it is with a variable, however I can't seem to make it work.

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @DanielB,

You can create measures like below:

LastVisit = CALCULATE(MAX('Table1'[Shop Visit]),ALLEXCEPT(Table1,Table1[Person]))

TimeSpent =
SUMX (
    VALUES ( Table1[Person] ),
    CALCULATE (
        SUM ( Table1[Time Spent] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Person] ),
            Table1[Shop Visit] = [LastVisit]
        )
    )
)

Best Regards,
Qiuyun Yu