Forum Discussion

dmbd1904's avatar
dmbd1904
Helper III
6 years ago

Cumulative Measure Using Other Measures

Hi, I've been struggling with this one for a few hours and would really appreciate some help...

 

I have the below table (In the image). 'The Forecast Cost' is a column. The other measures are included below. I'm trying to get the running total of the 'Combined' measure, but as you can see the total dips in September, when it shouldn't.

 

Starting Point =

var StartDate = [Earliest Month with Forecast]
Return
IF(ISBLANK([**bleep**. Forecast Cost]),
CALCULATE(
    SUM('Cairns Home Construction$Job Ledger Entry'[Total Cost]),
        'Calendar'[Month]<StartDate
    ),BLANK())
 
 
Combined = CALCULATE(SUM('Commercial Forecasts'[Forecast Cost]))+[Starting Point]

 

Running Total =
CALCULATE(
    [Combined],
    FILTER(
        ALLSELECTED('Calendar'[Month]),
        ISONORAFTER('Calendar'[Month], MAX('Calendar'[Month]), DESC)
    )
)

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    dmbd1904 ,

    If total cost have no data when forecast data is there

    Combined = CALCULATE(SUM('Cairns Home Construction$Job Ledger Entry'[Total Cost]))+[Starting Point]

    else
    Combined = CALCULATE(if(max('Calendar'[Month])<StartDate,SUM('Cairns Home Construction$Job Ledger Entry'[Total Cost]),0))+[Starting Point]

    • dmbd1904's avatar
      dmbd1904
      Helper III
      6 years ago

      Hi amitchandak , thanks for your reply.

       

      I'm struggling to execute what you have suggested. I've tried the changing the 'Combined' measure to the below but this hasn't worked (See the image).

       

      Combined =
      var StartDate = [Earliest Month with Forecast]
      Return
      IF(AND(NOT(ISBLANK(CALCULATE(SUM('Commercial Forecasts'[Forecast Cost])))),ISBLANK([Starting Point])),
      CALCULATE(SUM('Cairns Home Construction$Job Ledger Entry'[Total Cost]))+[Starting Point],
      CALCULATE(if(max('Calendar'[Month])<StartDate,SUM('Cairns Home Construction$Job Ledger Entry'[Total Cost]),0))+[Starting Point])