Forum Discussion

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi KH11NDR,

     

    What does your raw data look like? I would suggest you create a date table first and try the formula below.

    Measure =
VAR previousEstimate =
    CALCULATE ( SUM ( Table1[Estimate] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        MIN ( Table1[Estimate] ) = 0,
        SUM ( Table1[Actual] ),
        IF (
            previousEstimate = 0,
            MIN ( Table1[Estimate] )
                - CALCULATE (
                    SUM ( Table1[Actual] ),
                    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
                ),
            MIN ( Table1[Estimate] ) - previousEstimate
        )
    )

    Rolling_calculeted_totals

     

    Best Regards,

    Dale

    • KH11NDR's avatar
      KH11NDR
      Icon for Helper IV rankHelper IV
      8 years ago

      Wow, 

       

      Thanks Dale, I'm new to this and now I know how important it is to show your data model exactly how it is, You've  got it working 100% as expected, It's my fault, In my model I have Estimate as down as a column, it's actually a calculated measure and when I do your measure, I get an error here "PREVIOUSMONTH('Master Calendar'[Date])".  Totally my fault, for not saying Estimate was a measure.  I hope it's a quick fix, as I can't work it out, but I'm working on it.

       


      Measure 11 =
      var previousRevenue =
      CALCULATE([Total to Earn - % Complete of Project]),PREVIOUSMONTH('Master Calendar'[Date]))
      return
      if (
      min ( [Total to Earn - % Complete of Project] ) = 0,
      sum( 'Calc Numbers'[Previous Revenue Submitted (£) - Original]),

      if( previousRevenue = 0,
      min([Total to Earn - % Complete of Project])
      - CALCULATE(sum('Calc Numbers'[Previous Revenue Submitted (£) - Original]),
      FILTER(ALL('Master Calendar'), 'Master Calendar'[Date] < min ('Master Calendar'[Date]))
      ),
      min([Total to Earn - % Complete of Project]) - previousRevenue

      )
      )

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi KH11NDR,

         

        You are welcome. Can you provide more information as I mentioned in the Message?

         

        Best Regards,

        Dale

    • KH11NDR's avatar
      KH11NDR
      Icon for Helper IV rankHelper IV
      8 years ago

      Is this question posted in the wrong forum? is there anywhere else I can post this question?

       

      Many Thanks