Rolling calculated totals

[KH11NDR]

[v-jiascu-msft]

Hi @KH11NDR,

What does your raw data look like? I would suggest you create a date table first and try the formula below.

Measure =
VAR previousEstimate =
    CALCULATE ( SUM ( Table1[Estimate] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        MIN ( Table1[Estimate] ) = 0,
        SUM ( Table1[Actual] ),
        IF (
            previousEstimate = 0,
            MIN ( Table1[Estimate] )
                - CALCULATE (
                    SUM ( Table1[Actual] ),
                    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
                ),
            MIN ( Table1[Estimate] ) - previousEstimate
        )
    )

Best Regards,

Dale

[KH11NDR]

Wow, 

Thanks Dale, I'm new to this and now I know how important it is to show your data model exactly how it is, You've  got it working 100% as expected, It's my fault, In my model I have Estimate as down as a column, it's actually a calculated measure and when I do your measure, I get an error here "PREVIOUSMONTH('Master Calendar'[Date])".  Totally my fault, for not saying Estimate was a measure.  I hope it's a quick fix, as I can't work it out, but I'm working on it.

Measure 11 =
var previousRevenue =
CALCULATE([Total to Earn - % Complete of Project]),PREVIOUSMONTH('Master Calendar'[Date]))
return
if (
min ( [Total to Earn - % Complete of Project] ) = 0,
sum( 'Calc Numbers'[Previous Revenue Submitted (£) - Original]),

if( previousRevenue = 0,
min([Total to Earn - % Complete of Project])
- CALCULATE(sum('Calc Numbers'[Previous Revenue Submitted (£) - Original]),
FILTER(ALL('Master Calendar'), 'Master Calendar'[Date] < min ('Master Calendar'[Date]))
),
min([Total to Earn - % Complete of Project]) - previousRevenue

)
)

[v-jiascu-msft]

Hi @KH11NDR,

You are welcome. Can you provide more information as I mentioned in the Message?

Best Regards,

Dale

[KH11NDR]

Anyone?

[KH11NDR]

Is this question posted in the wrong forum? is there anywhere else I can post this question?

Many Thanks