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 rolling total and I think it related to the way my model and date dimension table is setup. 

 

I have summary data of customer sales broken down by month by category. 

CustomerIDCategoryIDMonthYearSales
11092023$200.00
12092023$150.00
11102023$225.00

 

My Date (DateDim) table has the following structure.

MonthYearYearMonthFullMonthOrdinalReverseMonthOrdinal
122023December, 2023601
112023November, 2023592
102023October, 2023583
092023Septermber, 2023574

 

I created another Date table (DateDimX) just being a straight copy of first one.  DateDimX also related to Sales Summary table.

 

 

 

 

Sales $ = CALCULATE(SUM(SalesDeptSums[SalesDollars]))

 

 

 

 

My measure for [Sales $ RT] 

 

 

 

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

 

 

 

 

 

 

It is clear to me that I'm restricting data to last 12 months based on my filter, but no matter what changes i tried to make to [Sales $ RT] i can't get it display running total. Below is the desired output.

 

 

Please note that I want to control what is the last month to display my single selection of the filter.

 

 

 

 

 

 

Need Help

4 Replies

  • 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 .