Issue with totals on SUMX with GENERATE function

[paulalmond91]

I have a calculation that determines the total value of a sale that falls inside a single month. There is an added complication in that I require this calculation to divide its total between the number of months in which it falls.

For example:

Say I have 3 courses:

Course A - Starts in January 2018 and Ends in March 2018 - £250

Course B - Starts in February 2018 and ends in February 2018 - £500

Course C - Starts in February 2018 and ends in March 2018 - £1000

My Calculation needs to assess each course against their two dates, take the total value divide by the duration in months and then apply that value in the months it is taking place.

So my expected conclusion is:

January 2018 Revenue - £83.33

February 2018 Revenue - £1083.33

March 2018 Revenue - £583.33

My measure works perfectly for each individual month however I am unable to produce a total measure that reflects the cumulative total (or essentially the sum of all months Revenue). 

This is the current measure:

Optimised Spread = SUMX('Nominal Entries',
CALCULATE(SUM('Nominal Entries'[Value])/[Values],
Accounts[Account ID]<>4064,
Events[Course ID]<>495,
Events[Course ID]<>496,
GENERATE(VALUES('Date'[Date]),
FILTER('Events',
CONTAINS(DATESBETWEEN('Date'[Date],Events[Start Date],Events[Actual End])
,'Date'[Date],
'Date'[Date])))))))

Some details here: [Values] relates to the # of months between start/end dates. The Course ID filtering is to remove errant courses not applicable to this calculation.

I have created a date table to use as an index as follows:

let
    DateList = List.Dates(#date(2011,9,30), 3650, #duration(1,0,0,0)),
    AddColumns = List.Transform(
                 DateList,
                 each
                 {_, Date.Month(_), Date.Year(_)}
                 ),
    DateTable = #table(type table[Date=date, Month=number, Year=number], AddColumns)
in
    DateTable

I am left with the following results:

When I add each row I end up with £115,781.17 rather than the grand total.

I've racked my brain for hours and done a bit of googling but cant find a solution. To note, each row is working out correct - its simply the total value that does not add up correctly.

[v-yulgu-msft]

Hi @paulalmond91,

Here is a thread providing suggestions to deal with Measure Totals. Please check whether it helpful to you.

Regards,

Yuliana Gu

[paulalmond91]

Thanks for this.

I did review the details but none of the suggestions seemed to help in my scenario.

This is because the filters are generated by the GENERATE function and there are no slice filters being applied.

[PaulDBrown · Accepted solution]

@paulalmond91,

Try this and see if it works (I’m assuming your date table has a month column):

Totals = SUMX(SUMMARIZE(‘Date’, Date[Month]), [Optimised Spread])

(Apologies if there is a bracket missing or some other syntax error; I’m typing this on a phone...)

[paulalmond91]

This is perfect!

Thank you so much!