Cumulative Total

[pierredelareux]

Hi,

I know this topic has been treated but I have a special request.

Thanks to article I found the way to get the cumulative total as a mesure like below :

Budget Corrigé Cumulé =
VAR CurIndex = MAX('S-Curve'[Index])
RETURN
CALCULATE(SUM('S-Curve'[Budget Corrige]);FILTER(ALL('S-Curve');'S-Curve'[Index]<=CurIndex))

There is another colum in is tab : PHASE.

Whe n I select a PHASE value the cumative total is false.

I've tried to filter by PHASE but it doesn't help.

Thanks

[TomMartens]

Hey,

you should sightly rearrange your model a little and add an additional table "Index". This table then takes the roll of the date table.

Please have a closer look here: http://www.daxpatterns.com/cumulative-total/

You are using ALL(table) in your calculation, that "overrules" the filter from the phase column, maybe you should also consider to create a phase table, then you're model takes the form of a star schema (dimension tables and fact tables).

From my experience a lot of of calculations become difficult or even impossible, if there is just one table.

Hope this helps

Tom

[pierredelareux]

Please find belowmy screenshot.

My goal is to get the cumul of "Budget Corrige" and "Dep Reelles" for the first to the last day.

Then I wan to be able to get the result for one "IMPUT:PHASE"

Thanks

[TomMartens]

It will be very helpful if you can share a simplified Excel Workbook that contains sample data.

Here is a link to a post that describes how to create sample data, if it's not possible to share an Excel workbook:

https://community.powerbi.com/t5/Desktop/Please-use-Table-FromRows-to-create-sample-data/m-p/111557

[pierredelareux]

Please find the CSV to download here : Link

Pierre

[TomMartens]

Hey,

sorry for the delay, I had to take care of some other issues 🙂

I guess this does the trick:

Budget **bleep** = 
var maxIndex = calculate(LASTNONBLANK('data'[Index], sum(data[Budget Corrige])))
var minIndex = calculate(FIRSTNONBLANK(all('data'[Index]), sum(data[Budget Corrige])))
return
calculate(
sum('data'[Budget Corrige]), FILTER(all(data[Index]),
	'data'[Index] >= minIndex && 
	'data'[Index] <= maxIndex)
)

Here you can find a pbix

Maybe you already know this, but if not it is an excellent read about filtering tables: https://www.sqlbi.com/articles/filtering-tables/

Hope this helps

[pierredelareux]

Hi,

I had to adhust your code (filter the sum on table and not column) to get a sum as : 

Budget **bleep** = 
var maxIndex = calculate(LASTNONBLANK('data'[Index], sum(data[Budget Corrige])))
var minIndex = calculate(FIRSTNONBLANK(all('data'[Index]), sum(data[Budget Corrige])))
return
calculate(
sum('data'[Budget Corrige]), FILTER(all('data'),
	'data'[Index] >= minIndex && 
	'data'[Index] <= maxIndex)
)

When I select a "IMPUT:PHASE" it doesn't give me right total amount.

Sorry to bother you...

Pierre

[TomMartens]

No problem,

but I have to admit that I do not totally understand what the result should look like

a) when you do not slice by a phase

b) when you slice by a phase

maybe you can provide a very simplistic excel file with two sheets

sheet1 - data

3 phases with 3 to 4 indexes each and corresponding values (budget) including zeros

sheet2 - made up results

[pierredelareux]

Hi,

Find here the Excel I made.

One sheet : data / the other dynamic cross table to explain you.

a) when you do not slice by a phase --> cumulative total budget and cumulative total expenses

b) when you slice by a phase --> cumulative budget of the selected "phases" and cumulative expenses of the selected "phases"

Pierre

[TomMartens]

Hey,

not sure if I'm still missing some important aspect 😉 but maybe ...

Here is a link to a new pbix file

And here a little screen shot that looks pretty much the same as from your Excel sheet

Maybe

[pierredelareux]

It is what I'm looking for.

But I can't set it in mine. Power BI reject telling me there is to many values in [Date]. I have to use an aggregate function.

Which one do you advise me to take ?

Regards,

Pierre

[TomMartens]

What, ...

You are saying that the Power BI file that uses the reduced dataset of your "made up" Excel file works as you expected, but as soon as you take one of the Formulas, lets say "BUDGET SUM" to your Power BI file

BUDGET SUM = 
CALCULATE(SUM(data[BUDGET]),
	FILTER(ALL('data'[DATE]),'data'[DATE] <= MAX('data'[DATE]))
)

Power BI Desktop throws an error ??? this is odd.

I'm using the lastest Power BI Desktop version "2.47.4766.801 64-bit (June 2017)", there has been an update, to the 1st June release due to some errors.

Some other questions

• I assume that you are using ";" as a separator for formula arguments, did you change the "," (both) to ";"
• How many rows does your Dataset contain, is it the same as in your data.csv
• Are you sure that your values in the [Date] column are valid dates

 
Can you please provide a Screenshot of your adjusted formula and of the error message, maybe you encountered a bug

[pierredelareux]

Find my answers below.

Some other questions

• I assume that you are using ";" as a separator for formula arguments, did you change the "," (both) to ";" YES
• How many rows does your Dataset contain, is it the same as in your data.csv - 4120 lines
• Are you sure that your values in the [Date] column are valid dates - I change null values to "01/01/2016"

Probelm is still occuring

Pierre