Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sales Velocity Running Total

Hello,

 

I am having trouble figuring out how to create a running total measure from another measure over time.

 

The chart below shows the Sales Velocity (measure) for each quarter in 2019. Sales Velocity is a measure of columns from the table. I would like to know the running total of my Sales Velocity as the year goes on. When I create another measure for the Running Total, I get these random numbers.

 

Running Total = 
SUMX(
    FILTER(
        ALL('Sales Data'[Date]),
        'Sales Data'[Date] <= MAX('Sales Data'[Date])
    ),
    'Sales Data'[Sales Velocity]
)

This is wrong. What I need is:

How can I update my measure to reflect the correct values?

 

Thanks in advance!

Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    I simplified the formula, below an updated one

     

    LC

     

    Running Total 2 = 
CALCULATE('Sales Data'[Sales Velocity],
        FILTER(ALL('Sales Data'),
            'Sales Data'[Date] <= MAX('Sales Data'[Date])
        )
)

5 Replies

  • lc_finance's avatar
    lc_finance
    Icon for Solution Sage rankSolution Sage
    6 years ago

    hi Anonymous ,

     

     

    can you share the DAX formula you used for the measure 'Sales Velocity'?

     

    Regards,

     

    LC

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sure, here is dummy data for how I came up with the measures:

       

      DateOpportunityAmountWin RateCycle Time (Days)
      1/6/2019A         6,340Lost74
      1/31/2019B         6,276Won72
      2/3/2019C         7,710Lost30
      2/7/2019D         5,915Won87
      2/9/2019E         5,772Won38
      3/4/2019F         5,961Won58
      3/8/2019H         4,071Won52
      3/26/2019I         7,606Won40
      4/2/2019J         2,637Lost72
      4/5/2019K         7,576Lost72
      4/18/2019L         5,594Won25
      4/22/2019M         2,251Won30
      4/28/2019N         3,393Won51
      5/6/2019O         1,743Lost49
      5/12/2019P         1,561Lost73
      5/17/2019Q         4,962Won16
      5/23/2019R         2,460Won38
      5/26/2019S         1,123Lost65
      6/3/2019T         3,786Won84
      6/10/2019U         2,838Won88
      6/19/2019V         8,255Won71
      6/27/2019W         7,750Won54
      7/2/2019X         6,916Won29
      7/15/2019Y         5,212Won19
      7/31/2019Z         6,951Lost90
      8/2/2019AA         1,073Won29
      8/14/2019BB         2,406Won15
      8/28/2019CC         7,072Lost66
      9/13/2019DD         8,522Won76
      9/22/2019EE         2,820Won79
      10/5/2019FF         8,872Lost21
      10/8/2019GG         3,193Won61

       

      Sales Velocity = [Opportunity Count]*[Deal Amount]*[Rate]/[Sales Cycle]

      where

      Opportunity Count = DISTINCTCOUNT('Sales Data'[Opportunity])
      Deal Amount = AVERAGE('Sales Data'[Amount])
      Rate = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Sales Data'[Opportunity]),
        FILTER(
            'Sales Data',
            CONTAINSSTRING('Sales Data'[Win Rate], "won")
        )
    ),
    [Opportunity Count]
)
      Sales Cycle = AVERAGE('Sales Data'[Cycle Time (Days)])

       

      • lc_finance's avatar
        lc_finance
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Hi Anonymous ,

         

        I believe this is what you are looking for.

         

        The 'running total 2' for Q4 (2024.11) now matches the total year for 'Sales Velocity' (2024.11)

         

        Let me know

         

        LC

         

        Running Total 2 = 
CALCULATE('Sales Data'[Sales Velocity],
        ALL('Sales Data'[Date]),
        FILTER(ALL('Sales Data'),
            'Sales Data'[Date] <= MAX('Sales Data'[Date])
        )
)