Forum Discussion

pierredelareux's avatar
pierredelareux
Helper I
9 years ago

Cumulative Total

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 CurIn...
Need Help
Show and Tell
Tutorial Requests
TomMartens's avatar
TomMartens
Super User
9 years ago

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's avatar
pierredelareux
Helper I
9 years ago

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

    • pierredelareux's avatar
      pierredelareux
      Helper I
      9 years ago

      Please find the CSV to download here : Link

       

      Pierre

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        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