Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Proud to be a Super User!
Paul on Linkedin.
Hi @paulalmond91,
Here is a thread providing suggestions to deal with Measure Totals. Please check whether it helpful to you.
Regards,
Yuliana Gu
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.
Proud to be a Super User!
Paul on Linkedin.
This is perfect!
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |