Forum Discussion
Solved
Spilled formula calculating revenue by quater or year
Hi,
I am trying to create a formula takes all project from a table and spills those projects in revenues per quarter or year (both would be handy to have) and chops the revenues in those quarters or years accordingly. See the attached simple example.
I know that I can use sequence in order to spill a number of months from s startindate and calculate the revenue per month, but not sure how to group that the right way.
Somebody any advice how to approach?
Many thanks!
Perhaps something along these lines:
=LET( i, IF(tbl_Projecten[Duration],tbl_Projecten[Duration],1), j, SEQUENCE(,MAX(i),0), t, i>j, p, TOCOL(IFS(t,tbl_Projecten[Project]),2), v, TOCOL(IFS(t,tbl_Projecten[Revenues]/i),2), d, TOCOL(IFS(t,EDATE(+tbl_Projecten[Startdate],j)),2), q, QUOTIENT(MONTH(d)-1,3)+1, y, YEAR(d), a, PIVOTBY(HSTACK(y,q),p,v,SUM,0,2,,1), VSTACK(HSTACK({"Year","Quarter"},DROP(TAKE(a,1),,2)),DROP(a,1)) )See also the Conditional Formatting rules in the attached file...
5 Replies
Perhaps something along these lines:
=LET( i, IF(tbl_Projecten[Duration],tbl_Projecten[Duration],1), j, SEQUENCE(,MAX(i),0), t, i>j, p, TOCOL(IFS(t,tbl_Projecten[Project]),2), v, TOCOL(IFS(t,tbl_Projecten[Revenues]/i),2), d, TOCOL(IFS(t,EDATE(+tbl_Projecten[Startdate],j)),2), q, QUOTIENT(MONTH(d)-1,3)+1, y, YEAR(d), a, PIVOTBY(HSTACK(y,q),p,v,SUM,0,2,,1), VSTACK(HSTACK({"Year","Quarter"},DROP(TAKE(a,1),,2)),DROP(a,1)) )See also the Conditional Formatting rules in the attached file...
Many thanks, I think I can use this as a basis! 🙏
Likely possible with modern dynamic array and lambda functions but I'm not sure that it will be a very easy solution. I prefer Power Query for summaries like this as it's relatively straightforward. See attached.
great many thanks for your reply! Might be chosing for a pq solution, although I try to get to a dynamic array where the data in some of the tables is then immediately updated (after changing some input). regards, Michiel
Great solution! Thx for your input (might be chosing to go for a PQ solution), but I was also hoping to fetch this in a dynamic array formula, where the data that is modified is immediately updated in some other tables.
