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
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

Anonymous 

 

so from what i undestand , your data is at the month-year level granularity.

 

fist go to power query , change datatype for dimdate column to date : 

you will get an error for the last row . ( i took the sample data you have shared ) . 

so fix it from the raw data . 

 

add 2 columns using power query : 

year column and month column (  these are easy to do ) . 

now you use the following measure : 

Sales $ RT =
VAR MaxOrd = CALCULATE(MAX('DateDim'[MonthOrdinal]))
VAR MinOrd = MaxOrd - 11

VAR Results =
    CALCULATE (
        [Sales $],
        'DateDimX'[MonthOrdinal] <= MaxOrd,
        'DateDimX'[MonthOrdinal] >= MinOrd,
        REMOVEFILTERS(DateDim)
    )
RETURN
    Results

 

 

hope it works for you.

if not, please share your power bi file so i take a look and assist you into achieving your desired output . 

 

 

 

 

 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

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
    Community 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