Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Problems with Running Totals

UPDATED: Adding the link to PBIX and data, as well as desired output on the bottom of the post   Link to PBIX File and data in CSV format    I'm having problems with creating a measure for rolli...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Daniel29195
I'm not sure what I'm missing. I was able to follow your suggestion all the way until the adding of two columns for Year and Month. But I was not sure how it plays the role inside of measure since the code for the measure is the same as before.

Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

 

Anonymous  sorry my bad . 

i copied yours to modify it, but it seems i didnt work .

anw

try using this one :

Sales $ RT =
VAR MaxOrd = CALCULATE(MAX('DateDim'[yearmonthfull]))
VAR year = year(MaxOrd)

VAR Results =
CALCULATE (
[Sales $],
'DateDimX'[yearmonthfull] <= MaxOrd,
year('DateDimX'[yearmonthfulll]= year ,
REMOVEFILTERS(DateDim)
)
RETURN
Results

 

 

 

tell me if it works for you . 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Daniel29195 
    I used your suggestion and it is still not doing running total instead just showing the total for each month. Addinitionally, your solution starts at the beginning of the year instead of going back 12 months. 

     

    Here is modified code that I used based on your feedback.

     

    Sales $ RT 1
VAR MaxDate = CALCULATE(MAX('DateDim'[FirstDayOfMonth]))
VAR CurrentYear = YEAR(MaxDate)
VAR Results =
    CALCULATE (
        [Sales $],
        'DateDim'[FirstDayOfMonth] <= MaxDate,
        YEAR('DateDim'[FirstDayOfMonth]) = CurrentYear,
        REMOVEFILTERS(DateDim)
    )
RETURN
    Results

     

    I also tried a different solution of using DATESINPERIOD, it still produces only the 12 months worth of data but does not make it into running total.

     

    Sales $ RT 2 =
VAR SelectedMonth = SELECTEDVALUE('DateDim'[FirstDayOfMonth])
VAR RollingPeriod = DATESINPERIOD('DateDim'[FirstDayOfMonth], SelectedMonth, -12, MONTH)
VAR Results =
    CALCULATE (
        [Sales $],
        RollingPeriod,
        REMOVEFILTERS(DateDim)
    )
RETURN
    Results