Forum Discussion

BabyBinki821's avatar
BabyBinki821
Helper I
4 years ago
Solved

Weekly Growth Rates

Hi

 

Another DAX measure that is driving me nuts. I have bar chart with cumulative accounts over time. I am tring to calculate the growth rate from week to the next. I cannot figure it out 😞 Any sugestions. And here is the measure that a kind person helpe me out with where I am getting the cumulative account amounts.

 

Total Ordering Accounts =
VAR FirstDateInSlicer =
MIN ( 'Date'[Date] )
VAR LastDateInFilter =
MAX ( 'Date'[Date] )
VAR CurrentDateInFilter =
MAX ( 'DATE'[WEEK END])
RETURN
CALCULATE (
COUNTA('Ordering Accounts'[FIRST ORDER]),
ALLEXCEPT ( 'Date', 'Date'[Date] ),
'Ordering Accounts'[FIRST ORDER]<= CurrentDateInFilter)

 

 

  • BabyBinki821's avatar
    BabyBinki821
    4 years ago

    Thank you!!! This worked perfectly. One question - some of the %s are off by one decimal - what would be the reason for that. Both my total ordering accounts and previous week are set as whole numbers.

  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi:

    I'm wondering if it's a rounding thing? 

    You could try some rounding functions like

    The following expression rounds 1.3 to the nearest multiple of .2. The expected result is 1.4.

    DAXCopy
     
    = MROUND(1.3,0.2)

    :Can you markthe initial question as solved? Thanks...

     

4 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    It looks like you have weekending or week no in your date table, which is great. Usinging week no. is easier for this measure. If you want to figure the week to week growth on the cumulative measure I beleive you can try these measures(2):

     

    Previous Week [Tot Ord Accts] = CALCULATE([Total Ordering Accounts], FILTER(ALL(Dates),
    Dates[Year] = SELECTEDVALUE(Dates[Year]) && Dates[Week No.]= SELECTEDVALUE(Dates[Week No.])-1))
     
    *Note - see how I have [Week No. Above]?  As a Calc col in your datetable 
    Week No. = WEEKNUM('Date'[Date])
     
    To figure % change from week to week:
    Weekly Change = 
    VAR weekdiff =  [Total Ordering Accounts] - [Previous Week [Tot Ord Accts]
    return
    DIVIDE(weekdiff, [Total Accounts Ordering],0]
     
    This is comparing cumulative week to prev cumulative week. If you want to do just do this on just week to week rresults (not on cumulative) you use your basic [Ordering Accounts] measure instead of the cumulative [Total Ordering Accounts] measure. This will give you the true week on week change.
     
    I hope this helps!
    • BabyBinki821's avatar
      BabyBinki821
      Helper I
      4 years ago

      Thank you!!! This worked perfectly. One question - some of the %s are off by one decimal - what would be the reason for that. Both my total ordering accounts and previous week are set as whole numbers.

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi:

        I'm wondering if it's a rounding thing? 

        You could try some rounding functions like

        The following expression rounds 1.3 to the nearest multiple of .2. The expected result is 1.4.

        DAXCopy
         
        = MROUND(1.3,0.2)

        :Can you markthe initial question as solved? Thanks...