cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lucasrm
Frequent Visitor

Find first date where a cumulative total condition is met

Hello community!

 

I'm struggling with a measure and would appreciate if anyone could help!

Here's what I'm trying to do:


I have a physical table of sales amount by employee by date like this:

Name | Amount | Date

John | 500 | 10/04/21
Amanda | 700 | 12/04/21
John | 400 | 15/04/21

I want to create a measure (no custom column allowed since I'm using a shared dataset) where, for each employee, I'll get the first date where they achieved cumulative 600 or more in sales. For Amanda, the result would be 12/04/21. For John, the result would be 15/04/21.

 

I tried creating a virtual table using SUMMARIZE with the cumulative sum and getting the first date in this table but it doesn't work.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@lucasrm 

please try this

Measure = 
VAR tbl=ADDCOLUMNS('Table (2)',"ytdamount",sumx(FILTER('Table (2)','Table (2)'[name]=EARLIER('Table (2)'[name])&&'Table (2)'[date]<=EARLIER('Table (2)'[date])),'Table (2)'[amount]))
return MINX(FILTER(tbl,[ytdamount]>600),'Table (2)'[date])

please see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@lucasrm 

please try this

Measure = 
VAR tbl=ADDCOLUMNS('Table (2)',"ytdamount",sumx(FILTER('Table (2)','Table (2)'[name]=EARLIER('Table (2)'[name])&&'Table (2)'[date]<=EARLIER('Table (2)'[date])),'Table (2)'[amount]))
return MINX(FILTER(tbl,[ytdamount]>600),'Table (2)'[date])

please see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

That's it! Thank you very much! I had no idea we could set a virtual table to a variable.

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!