Calculating the last non blank value not totaling correctly

[ghunt03]

I am trying to get the last non blank value from a table of entries by month. The issue I am having is I can't get the grand total to work correctly 

Estimated Cost @@ Completion - Last Reported = 
var minDate = MIN('Calendar'[Date])
var totalCost = CALCULATE (
    (SUM ( 'Work In Progress'[Cost] ) + SUM('Work In Progress'[CostAdjustment])),
    LASTNONBLANK ( FILTER(ALL(Calendar[Date]), 'Calendar'[Date] <= minDate), CALCULATE ( SUM ( 'Work In Progress'[Cost] ) +  SUM ( 'Work In Progress'[CostAdjustment] ) ) )
)

return totalCost

I am using the minimum date because I am trying to calculate the last reported value for the selected period.

Any help would be greatly appreciated

[v-jiascu-msft]

Hi @ghunt03,

Can you share the file? Please mask the private parts first.

Why did you use LASTNONBLANK?

Maybe the formula could work.

Estimated Cost @@ Completion - Last Reported = 
var minDate = MIN('Calendar'[Date])
var totalCost = 
    LASTNONBLANK ( FILTER(ALL(Calendar[Date]), 'Calendar'[Date] <= minDate), SUM ( 'Work In Progress'[Cost] ) +  SUM ( 'Work In Progress'[CostAdjustment] ) 
)

return totalCost

Best Regards,

Dale

[ghunt03]

Hi @v-jiascu-msft

I was using the LASTNONBLANK formula to find the latest reported value. The formula you suggested only seems to return a date

I have a copy of the file available at the following link

Example File

The other option I have tried is to lookup the Cost and Cost Adjusment value as there is only a maximum of one entry per month.  However I haven't been able to figure out the correct syntax for this one either

[v-jiascu-msft]

Hi @ghunt03,

I wonder if the solution below could help.

1. Create a new table "Contract" due to not all the contract exists in all the months.

Contract = DISTINCT(VALUES('Work In Progress'[Contract]))

2. Create a calculated table.

Result =
SUMMARIZECOLUMNS (
    Contract[Contract],
    'Work In Progress'[Mth],
    "value",
    VAR total =
        SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] )
    RETURN
        IF (
            ISBLANK ( total ),
            CALCULATE (
                (
                    SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] )
                ),
                LASTNONBLANK (
                    FILTER ( ALL ( 'Work In Progress'[Mth] ), 'Work In Progress'[Mth] <= [Mth] ),
                    CALCULATE (
                        SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] )
                    )
                )
            ),
            total
        )
)

3. Create a visual based on the new table.

Best Regards,

Dale