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
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

       

      • pierredelareux's avatar
        pierredelareux
        Helper I
        9 years ago

        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