Forum Discussion

bender's avatar
bender
New Member
4 years ago

Calculating a running total on a dimensional string value

Hi everyone,

I am working through an issue trying to create a running total based upon a String value in my dimension.  I have a fact table with user records and a AgeSK value that links to a dimension table.  The running total I am attempting to build would give me counts based upon the current period in the dimension, and any records in later periods.  The basic data sets look as follows:

 

In a previous iteration of this, where I was predetermining the months, I was able to get away with a calculation that utilized the UnboundMonth string value because I created the values with a leading zero so that they would still order properly.  The calculation essentially looked like this (note in that previous iteration the PostSignupAgeSK was stored as a month count, in the above its stored as a day count):

 

TotPostSignupAgeMthRollingQualifiedUsers:=
VAR CurrentMonthNum = SELECTEDVALUE(Dim Table[UnboundMonth])
RETURN
CALCULATE(
[TotQualifiedUsers],
Dim Table[UnboundMonth] >= CurrentMonthNum,
ALL(Dim Table[UnboundMonth])
)

 

This formula won't work for the RetentionPeriod because the string data will not order appropriately to roll the data up sequentially.  What I essentially think needs to happen is I need to bring the AgeSK in to scope to order appropriately, but work from the RetentionPeriod value so that when I visualize the data, that is the label value present.  I found several solutions for doing this when the data points are all in the fact table, I just am unable to properly implement them when using a dimension.  Any and all help would be appreciated.

 

Thank you

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    bender , I think the above should work for RetentionPeriod , as long as UnboundMonth is sortable.

     

    You need a sort column for RetentionPeriod 

     

    This should work

    CALCULATE(
    [TotQualifiedUsers],
    filter( ALL(Dim Table[UnboundMonth]) , Dim Table[UnboundMonth] >= max(Dim Table[UnboundMonth] ) ,
    )
    )

     

     

    or

     

    CALCULATE(
    [TotQualifiedUsers],
    filter( ALL(Dim Table[UnboundMonth]) , Dim Table[UnboundMonth] <= max(Dim Table[UnboundMonth] ) ,
    )
    )

     

    How to Create Sort Column and Solve Related Errors:
    https://www.youtube.com/watch?v=KK1zu4MBb-c