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

  • 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