Hello I seem to be having an issue with my DAX formula. I have the total value of the contracts divided by 12 to equal the month GP & the total value divided by 365. Since all the contracts have different start and end dates I am trying to create a measure that will successfully add them to a total when they begin and subtract when the end date happens. Can anyone assist?
This is some sample data
Can you upload the actual file, or at a minnum the DAX formula you are authoring?
GPMONTHSUM = CALCULATE(
SUMX(
SUMMARIZE(‘Date’,‘Date’[Date],“GP_Monthly”,‘Measures Table’[Total GP Sum]), [DailyGPSum]),
FILTER(
ALLSELECTED(‘Date’[Date]),
‘Date’[Date]<=MAX(‘Date’[Date])))
Here is the formula that has the closest result. The problem seems to be with the “Daily GP” column, it’s adding all the values ($53,000)ish and then spreading that $53,000 over the year.
Since there are different start dates we can’t use the whole month to calculate GP, if we have a start date at a non 1st of the month then we need to prorate the GP.
Will likely need to see some type of demo file to really assist further here.
To me though your formula looks like a solid cumulative total pattern which seems fine.
Because we can’t see the whole model it’s hard to really grasp all factors at play.
One things though, within the SUMX are you reference the virtual column you’ve created because it doesn’t seem like you are using the same name?
Sam
Also, using Calculate, SUMX, SUMMARIZE while adding a calculated column, it will be extremely hard to follow the Row/filter context and the associated context transition. I’d break those out into variables, and use add columns to add a column to your summarized table, and then iterate over that. Something like:
Var _SummarizeTable=
SUMMARIZE ( 'Date', 'Date'[Date])
Var _AddColumns=
ADDCOLUMNS(
_SummarizeTable,
"GP_Monthly",
[Total GP Sum]
)
RETURN
But w/o seeing some data it’s hard to get the function just right
cumulativesales.pbix (166.7 KB)
Attached is the file, now when I use my summarize formula it appears to break the dax measure. Any help is appreciated
Ok we need some more help here to assist. Please check out the link below for how to get the best help on the forum.
On a quick review here’s a few tips.
Make some small updates to your naming conventions. Invoked function doesn’t mean anything. It should simply be dates.
Then if you setup your formula syntax well, you can quickly see there is an issue within the formula in that the SUMX doesn’t have all the details it needs to work.
GPMONTHSUM =
CALCULATE(
SUMX( SUMMARIZE('Dates', Dates[Date], "GP_Monthly", [TotalValue] ), [GP_Monthly] ),
FILTER( ALLSELECTED('Dates'[Date]),
'Dates'[Date]<=MAX('Dates'[Date])))
I’m not sure if this is ow correct because you haven’t really provide any images or details around what to expect, but this is all I can create right at this moment with what’s been given.
Thanks
Sam
Hi @kurzashane, a response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!