cancel
Showing results for
Did you mean:
Frequent Visitor

## First/Last Values - Cumulative Total not working

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors