I have a table which has a cost and cost adjustment per month for contracts. Once a contract is finished it won’t appear in future months. So I am using the following formula to get the last reported value
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
The formula seems to be working correctly fro each month however in the total of the table it doesn’t add up correctly.
Any help would be greatly appreciated.
In order to get the totals to match your probably going to have to use the SUMMARIZE function wrapped in your current DAX formula. Here is a good YouTube video of Sam’s to watch for a possible solution called Fixing Complex Total Errors - DAX Formula Concepts": https://www.youtube.com/watch?v=PbkWkyKtAEw&t=2s
Yes can you give the technique showcased in this video a go and you should find the solution.
If not, place in here where you get to and can check what adjustments may be required.
Sam
I tried out the following formula but it appears that it isn’t returning the last non blank total. I think the issue is with the filtering but I am not sure how to fix it
Estimated Cost @ Completion - Summary =
var maxDate = MAX('Calendar'[Date])
var summary = SUMMARIZE(VALUES('Work In Progress'[Contract]),
"Cost",
CALCULATE ([Cost @ Completion], LASTNONBLANK ( FILTER(ALL(Calendar[Date]), 'Calendar'[Date] <= maxDate), [Cost @ Completion] ))
)
var totalCost = SUMX(summary,[Cost])
return totalCost
looking at contract 16030- have an entry in April so I would like it to use the value in March
I wouldn’t use LASTNONBLANK in the formula here. I find you rarely need to use this.
Is there any reason you can just filter by the MAX date.
I would rework this a bit to simplify it. It seems like there’s more going on in the formula than there needs to be.
VAR MaxDate = MAX( Date actually in fact table of contracts)
RETURN
CALCULATE( SUM( Cost column),
FILTER( ALL( Calendar ), Date Column = MaxDate Variable )
Can’t it be simplified to this?
Sorry about the formatting but I can’t replicate this in models I have currently.
Maybe there might need something additional to this for total again but I’m not sure. I just want to see why this simplified version isn’t enough first
Hi Sam
Thanks for the help with this I think I am definitely over complicating it. The reason I was using the LASTNONBLANK is because I was filtering based on the month but wanted to show what had previously been reported
I tried changing the formula to:
Estimated Cost @ Completion - Summary =
VAR MaxDate = MAX( 'Work In Progress'[Mth])
RETURN
CALCULATE( [Cost @ Completion],
FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate ))
It works until I add a filter to the Work In Progress month.
I have a sample version of what I am working on
Sample File
So when I work out things like this I always try to break each part out and check behaviour.
First thing I’m going to do it break out the date and see if I can get that right and actually view it in the table.
As you explain this isn’t working for what you need
So what we need to do is ignore the context of the date table somehow.
This is certainly to me the correct pattern to get this. It’s just the maxdate variable that needs to be worked on
Estimated Cost @ Completion - Summary =
VAR MaxDate = MAX( 'Work In Progress'[Mth])
RETURN
CALCULATE( SUM( 'Work In Progress'[Cost] ),
FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate ))
Is this what you need?
Maybe this is working. You’ll have to let me know.
Here’s the full formula
Estimated Cost @ Completion - Summary =
VAR MaxDate = CALCULATE( MAX( 'Work In Progress'[Mth] ), ALL( 'Calendar' ) )
RETURN
CALCULATE( SUM( 'Work In Progress'[Cost] ),
FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate ))
That’s my thought process around this anyway. Hopefully that helps in how to work these out in the future
Chrs
Hi Sam,
So close, there is just an issue with the totalling I tried to bring in the summarize function, however it seems to be excluding 16030 when looking at April (see the last column)
var MaxDate = CALCULATE( MAX( 'Work In Progress'[Mth] ), ALL( 'Calendar' ) )
return SUMX(SUMMARIZE(VALUES('Work In Progress'[Contract]),
"Cost",
CALCULATE( SUM( 'Work In Progress'[Cost] ),
FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate ))),[Cost])
I think this might do it
Completion (Total Correct) =
VAR MaxDate = CALCULATE( MAX( 'Work In Progress'[Mth] ), ALL( 'Calendar' ) )
RETURN
IF( ISFILTERED( 'Work In Progress'[Contract] ),
CALCULATE( SUM( 'Work In Progress'[Cost] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate )),
SUMX( SUMMARIZE( VALUES( 'Work In Progress'[Contract] ),
"CompletionAmt", CALCULATE( SUM( 'Work In Progress'[Cost] ), FILTER( ALL( 'Calendar' ), 'Calendar'[Date] = MaxDate ) ) ),
[CompletionAmt] ) )
I’m not sure what the total needs to be if this still isn’t right.
You’ll have to advise.
It seems exactly the same as the previous solution to me actually, so I’m not sure what’s missing here currently if this doesn’t solve it.
Hi Sam,
Sorry to keep bugging you about this however the total should be $ 10,477,360.38.
The numbers are showing correctly it is just the bottom total
Ok got it.
This is actually the one formula you can use that’s gets it right.
These totals can be difficult I know. They stump me also sometimes.
This isn’t the easiest solution but it works.
Testing =
VAR DateContext = ALL( 'Calendar' )
RETURN
SUMX( CALCULATETABLE(
SUMMARIZE( 'Work In Progress', 'Work In Progress'[Contract],
"CompletionAmt", CALCULATE( SUM( 'Work In Progress'[Cost] ),
FILTER( ALL( 'Calendar' ),
'Calendar'[Date] = CALCULATE( MAX( 'Work In Progress'[Mth] ), ALL( 'Calendar' ) ) ) ) ), DateContext ),
[CompletionAmt] )
It’s just all about context.
I’ll have to continue to make a lot more content around totals I think. It can be tricky…
1 Like
Hi Sam,
Thank you so much for your help with this. I really appreciate it. It does make me feel a bit better knowing that it can also stump someone who knows what they are doing
Gareth
1 Like
