Forum Discussion

MichielS340's avatar
MichielS340
Brass Contributor
May 22, 2025
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 o...
community
excel
Formulas and Functions
  • djclements's avatar
    djclements
    May 23, 2025

    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...

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 23, 2025

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.

 

MichielS340's avatar
MichielS340
Brass Contributor
May 23, 2025

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.

Resources