Forum Discussion

niculeica's avatar
niculeica
Helper I
2 years ago
Solved

Calculate Measure1 as Measure2 + Measure3 Running Total

Hi,

 

I'm trying to calculate Measure1 (Total Value) as Measure2 (Signed Contracts) + all backlog of Measure3 (Pending Contracts), everything displayed by date hierarchy (Year, Quarter, Month). So Measure3 would basicaly need to be transfromed into a Running Total of Pending Contracts.

 

Another way to put it would be Measure1 of March would be equal to signed Contracts of March plus March, Feb, Jan etc. (backwards) Pending Contracts.

 

Any suggestion? Thank you!

 

 

 

Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download the solution

    Download PBIX 

     

    How it works ...
    Create a Calandar table

     

    Create a measure

    YTD cumulative = 
VAR myyear = SELECTEDVALUE('Calendar'[Year])
VAR myperiod = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Facts[Amount in company currency]),
    ALL('Calendar'),
    'Calendar'[Year] = myyear &&
    'Calendar'[Date] <= myperiod 
)

     

    Dsiplay in matrix

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thnak you.

10 Replies

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    We want to help you but your description is too vaugue. Please write it again clearly

    Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
    Also provide the example desired output, with a clear description of the process flow.

    Remember not to share private data ... we don't want you to get into trouble. 😧

    Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

    You will get a quick response if you put time, care and effort into writing clear problem descriptions.

    Vaugue descriptions can waste your time and ourtime.

    Look foward to helping you when the above information is forthcoming

  • niculeica's avatar
    niculeica
    Helper I
    2 years ago

    Hi, speedramps,

     

    Ok, so I have 2 measures already calculated. One is Signed contracts, which is the amount of "active contracts" (based of opportunity stage) and the other one is Pending contracts.

     

    The matrix is displyed by Date hierarchy, with only Year, Quarter and Month (no Day). Signed contracts and Pending contracts are displayed correctly (data matches what I have in our CRM) per each month and quarter (SUM of Q = SUM of Months). I have pasted the previous screenshot thinking it's suggestive enough.

     

    I think I might've added too much info, fair enough... basically what I'm trying to calculate is a Running Total of Pending Contracts (so I can simply sum it up with Signed Contracts; so my ultimate goal is sum of Signed contracts per date + sum Running Total Pending contracts per date). Below is the fashion I'd like to have Pending Contracts calculated (Amount is the individual sum per each time period, i.e. Month, but the Total interests me. It's what I'm trying to find the measure for. So it would be a Measure based Running Total (I think), and not Column based. Based on Amount, which I already hava the data for.

     

    It's a little hard to comb through the data and attach it, as I'm talking 100k+ entries, sorry.

     

     

    • speedramps's avatar
      speedramps
      Super User
      2 years ago

      Please can you just simple provide input data, desired output data and brief clear description.
      I want to help but you are wasting your time and ours with vague waffle. ğŸ™„

      • niculeica's avatar
        niculeica
        Helper I
        2 years ago

        speedramps, at the very bottom is a data sample.

        Needed:

        - one measure = sum of Amount per year, quarter and month (date hierarchy) - in matrix;

        - one measure = cummulative sum of Amount per year, quarter and month;

        - desired output (cummulative sum of last month of q is equal to cummulative sum of q) right below. I hope it makes sense.

         

         SumCummulative Sum
        Jan1010
        Feb3040
        Mar60100
        Total Q1100100
        Apr30130
        May25155
        Jun20175
        Total Q275175

         

         

         

        Record IDDeal NamePipelineDeal StageAmount in company currencyClose DateInactive Date
        15860859474Deal no. 142Renewals - ARR>365 Days19942024-12-01 02:002024-12-01
        15842177351Deal no. 124Renewals - ARR<365 Days15182024-11-01 02:002024-11-01
        15678677277Deal no. 40Renewals - ARR<365 Days9992024-10-01 03:002024-10-01
        15611462181Deal no. 269Renewals - ARR>365 Days9952026-09-05 03:002026-09-05
        15557243932Deal no. 282Renewals - ARR<365 Days9282024-11-01 02:002024-11-01
        15541818683Deal no. 83Renewals - ARR<365 Days5242024-11-01 02:002024-11-01
        15516131724Deal no. 195Renewals - ARR>365 Days15292026-10-01 03:002026-10-01
        15425010614Deal no. 246Renewals - ARR<365 Days18302024-11-01 02:002024-11-01
        15421474859Deal no. 154Renewals - ARR>365 Days18242025-01-01 02:002025-01-01
        15412867601Deal no. 77Renewals - ARR<180 Days12142024-03-10 02:002024-03-10
        15279595559Deal no. 193Renewals - ARR<90 Days10272024-01-01 02:002024-01-01
        15164440081Deal no. 58Renewals - ARR<270 Days13342024-06-01 03:002024-06-01
        15164232203Deal no. 119Renewals - ARR<90 Days8462024-01-01 02:002024-01-01
        15118337967Deal no. 51Renewals - ARR>365 Days7162026-03-01 02:002026-03-01
        14994331225Deal no. 118Renewals - ARR<180 Days6542024-03-01 02:002024-03-01
        14992063493Deal no. 67Renewals - ARR<90 Days14182024-01-01 02:002024-01-01
        14982132624Deal no. 173Renewals - ARR<270 Days5062024-07-01 03:002024-07-01
        14981603117Deal no. 208Renewals - ARR<365 Days10782024-09-01 03:002024-09-01
        14980389804Deal no. 288Renewals - ARR<30 Days13502023-10-01 03:002023-10-01
        14932638466Deal no. 270Renewals - ARR<30 Days5162023-09-01 03:002023-09-01
        14873726787Deal no. 263Renewals - ARR<180 Days16932024-03-01 02:002024-03-01
        14824777142Deal no. 106Renewals - ARR<180 Days6152024-04-01 03:002024-04-01
  • niculeica's avatar
    niculeica
    Helper I
    2 years ago

    Not sure how helpful it is, but Pending Contracts is calculated as follows:

     

    1st Step, from the main Deals table:

     

    _Renewal amount raw =
    VAR CUR_STARTOFM =
    MAX(_Timeline[_Start M])
    VAR CUR_ENDOFM =
    MAX(_Timeline[_End M])
    RETURN
    CALCULATE(SUM(Deal[amount_in_home_currency]),
    FILTER(Deal,
    Deal[_Pipeline]="Renewals - ARR"
    && Deal[dealstage]<>"3980406"&&Deal[dealstage]<>"4081009"&&Deal[dealstage]<>"4081010"&&Deal[dealstage]<>"3980407"
    && Deal[original_deal_contract_item_end_date]+1>=CUR_STARTOFM
    && Deal[original_deal_contract_item_end_date]+1<=CUR_ENDOFM))
     
    2nd Step, in Timeline table where Deal[original_deal_contract_item_end_date]+1 is Summarized as main Column, to Sum months under quarters:
     
    _Renewal amount = SUMX(SUMMARIZE(_Timeline,_Timeline[_M],"Renewal ARR",[_Renewal amount raw]),[Renewal ARR])
     
    My Running Pending Contracts would have to be based on one of those two, I suppose. Currently the field in matrix the second formula, and I have the totals per each month, and not cummulatively.