Problems with Running Totals

[Anonymous]

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. 

CustomerID	CategoryID	MonthYear	Sales
1	1	092023	$200.00
1	2	092023	$150.00
1	1	102023	$225.00

My Date (DateDim) table has the following structure.

MonthYear	YearMonthFull	MonthOrdinal	ReverseMonthOrdinal
122023	December, 2023	60	1
112023	November, 2023	59	2
102023	October, 2023	58	3
092023	Septermber, 2023	57	4

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.

[Daniel29195]

@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]

@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]

@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]

@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