Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have created the following measure to calculate a running total
Sales Ann Proj Running = CALCULATE ( [Sales Ann Proj], FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
And it works wonderfully, I can see my running total per month.
I then add these monthly running totals to other measures to get my effective revenue per month.
However, when I want to see an aggregated overview of my revenue for the year I notice something is off.
That is because as soon as I move beyond a monthly view it is just giving me the latest running total instead of an aggregate of all the monthly running totals.
To hopefully make it more clear:
I need a way to get the sum of all monthly "Sales Ann Proj Running" instead of 119,882 which the running sum until the end of the year.
Regards
Cobus
Hi,
Do you mean this: 1 + (1 + 2) + (1 + 2 + 3) + (1 + 2 + 3 + 4) + … = Total. It seems that it doesn't make any sense for sales. There is a workaround you can try. Add a column in Date table. Use this formula as calculated column. Finally, you can add this column in the report.
Sales Ann Proj Running = CALCULATE ( [Sales Ann Proj], FILTER ( 'Date'[Date], 'Date'[Date] <= EARLIER ( 'Date'[Date] ) ) )
Best Regards!
Dale
@v-jiascu-msft
Version:1.0 StartHTML:000000256 EndHTML:000003106 StartFragment:000002019 EndFragment:000003074 StartSelection:000002019 EndSelection:000003070 SourceURL:https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/79431
@v-jiascu-msft
Hi Dale,
Thanks for the reply.
Perhaps I should explain what it is for better.
A portion of each month's sales are attributed as "annuity" - this means 15% of each months sales get carried over for all months following until the member cancels their "subscription"
i.e.
2017-01: Sales of R1000
2017-02: Annuity of R150
2017-03: Annuity of R150
2017-04: Annuity of R150
etc
Thus my revenue at the end of the year is:
Sales + Annuity
And because my table only includes the original sale date I have created a running annuity column.
It all works very well until I need to see the total.
I have tried your solution
Unfortunately I don't want the running total per date, but per month
Regards
Cobus
Hi,
At least the result of 2017-03 should be right. So there may be something context trick in the formula of [Sales Ann Proj]. Could you please post the formula here? The idea is very simple. We can't get the total of a measure, but we can get the total of a column. So we adjust the formula to run it as a calculated column. Then we will get the total in the report. Please have a try.
Best Regards!
Dale
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |