Iterative formula for total on columns

[Dave]

So I am having an issue where by I need to use an iterative formula to give me some totals but not by row as shown in various examples, but by column on a matrix and I cannot figure it out

From image and measure code below you can see that I have row totals working via a SUMX (measure fixed) but my column totals are not (see concrete or excavators). This is because the measure I am using checks for the date in the header to see whether it is in the past, current month or future and works out what values to show accordingly.

The measure fixed formula is what is shown in the matrix

The total column has no date reference so simply uses all three parts of my measure formula to return all values and not just the ones showing in the matrix. Any suggestions?

[sam.mckay]

To me the SUMX is the problem here as you have already worked out.

There’s just way to much going on in that second formula for me to comprehend every variable without also see the model to.

Is there any way to really simplify this formula. Seems quite long to be honest. I rarely, if ever end up with formulas that look like this.

It’s because I always look to simply build out more complex logic using measure branching and then also use variables to make it easier to read.

Are you able to do this also? I dare say you’ll work it out yourself as you do it, as it will simplify it for you also.

Let me know

[Dave]

Sam

Thanks for the pointer. I realised that the nested if statement approach (from my Excel days) was not filtering out the months properly when it came to totalling up the long winded measure.

I split that measure into three smaller, much more manageable measures that look at past, present and future in turn - each of which was a calculate statement that filtered out the relevant periods.

I then added the three measures back together again and all is good!!

I also think having a weekend away from the project helped clear my head a bit

Cheers, Dave

[sam.mckay]

Ok that’s great. Chrs