Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DanielB
Frequent Visitor

First/Last Values - Cumulative Total not working

Hi All,

 

I am using a dataset similar to the following:

 

PersonShop VisitTime Spent
A120
A230
A340
B15
B210
B320
B430
C160

 

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
v-qiuyu-msft
Community Support
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]
        )
    )
)

 

q3.PNG

 

 

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.

View solution in original post

1 REPLY 1
v-qiuyu-msft
Community Support
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]
        )
    )
)

 

q3.PNG

 

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.