Search code examples
powerbimeasure

Cumulated total of a measure in POWER BI

Hi have a shipment table where I create a table like below: Cumulated Hours 1st year: calculate the shipment of the year ComulativeSpedizioni_1_year_before: calculate a cumulative of the shipment 1year before Cumulated hours: it sums the Cumulated Hours 1st year and ComulativeSpedizioni_1_year_before

Then I would have running total of cumulated hours so I create the measure Cumulatad Hours running total as

Cumulated Hours Running Total = 
SUMX(
    filter(
        
        ALL(  'Spedizioni'[Data fattura] ), 'Spedizioni'[Data fattura] < MAX ( 'Spedizioni'[Data fattura] )
        ),
        [Cumulated Hours]

    )

But it doesn't work as it doesn't run a cumulative but it report the year data (for example 2020 should be 21450+16396+5995=46541 instead of 24120

Any suggestion?

My measure are:

Cumulated Hours Life  1st Year = 
   CALCULATE (
            SUM ( Spedizioni[Qtà fatturata] )
    )

ComulativeSpedizioni_1_year_before = 
    calculate(
        [ComulativeShip],
        dateadd('Calendar Lookup'[Date],-12,MONTH))


ComulativeShip = 
VAR comcares =
    CALCULATE (
        SUM ( Spedizioni[Qtà fatturata] ),
        FILTER ( ALL(  'Calendar Lookup' ), 'Calendar Lookup'[Date] <= MAX ( 'Calendar Lookup'[Date] ) )
    )
RETURN
    comcares




Cumulated hours = 
    [Cumulated Hours Life  1st Year]+[ComulativeSpedizioni_1_year_before]

Thank you

enter image description here

Solution

  • Try...

    Cumulated Hours Running Total = 
  var thisYear = MAX( 'Calendar Lookup'[Year] )
  return 
    CALCUALTE(
      SUMX(DISTINCT('Calendar Lookup'[Year]), [Cumulated Hours]),
      REMOVEFILTERS('Calendar Lookup'),
      'Calendar Lookup'[Year] <= thisYear 
    )