12mth running total with banding

DAX DAX Calculations
1

Hi Thanks to Harsh I have this visual
image

I want to switch the value caluclated to a rolling 12 months

I have this measure but I think this is missing the customer part therefore evaluating at the total.
like so
image

How do I get this to consider the rolling 12mths of sales per customer as in the original graphic. THe splikt shoud be the same as the filter placed on the visual is 31/12/2020

SalesR12 =
var MaxDate = MAX(Dates[Date])

Return

CALCULATE(
    [Total Sales],
            FILTER(
            ALL(Dates),
            AND(
                Dates[Date] <= MaxDate,
                DATEADD(
                    Dates[Date],1,YEAR)>MaxDate)))
2

Hi,

The measure look good to me if you can share work in progress power bi file we can look into it .
but your measure look fine to me.

Thanks,
Anurag

3

Hello @AliB,

Thank You for posting your query onto the Forum.

Is you banding logic still the same i.e. like it was in the previous post and only want to calculate the Last 12 Months Running Total? If so, then firstly your Running Totals measure should be something like this for Current and Previous Years -

Running Total Last 12M CY = 
CALCULATE( [Total Sales] , 
    DATESINPERIOD(
        Dates[Date] , 
        MAX( Dates[Date] ) , 
        -12 , 
        MONTH ) )



Running Total Last 12M LY = 
CALCULATE( [Running Total Last 12M CY] ,
    DATEADD( Dates[Date] , -1 , YEAR ) )

Once you create the above measures then you can consider them inside the Banding logic. Or if you don’t want to create Running Totals measures separately and want to add them inside the Banding logic then you can create the measures something like this for Current and Previous Years.

Sales per Banding L12M RT- CY = 
CALCULATE(
    CALCULATE( [Total Sales] , 
        DATESINPERIOD(
             Dates[Date] , 
             MAX( Dates[Date] ) , 
             -12 , 
             MONTH ) ) ,
     FILTER( VALUES( Customers[Customer] ) ,
          COUNTROWS(
               FILTER( 'Sales Banding' ],
                   [Avg Sales] >= 'Sales Banding'[Min] &&
                   [Avg Sales] < 'Sales Banding'[Max] ) ) > 0 ) )



Sales per Banding L12M RT - PY = 
CALCULATE(
    CALCULATE(
        CALCULATE( [Total Sales] , 
             DATESINPERIOD(
                 Dates[Date] , 
                 MAX( Dates[Date] ) , 
                 -12 , 
                 MONTH ) ) ,
         DATEADD( Dates[Date] , -1 , YEAR ) ) ,
     FILTER( VALUES( Customers[Customer] ) ,
          COUNTROWS(
               FILTER( 'Sales Banding' ,
                   [Avg Sales] >= 'Sales Banding'[Min] &&
                   [Avg Sales] < 'Sales Banding'[Max] ) ) > 0 ) )

Also rather than considering “Month & Year” category in a slicer you need to select only “Month” category since you already have segmentated or banded your data into the Matrix visual based on Years as well by putting them into the columns section.

The reason why your measure must not be rendering the correct result is because within the single “CALCULATE()” function you’re putting two conditions i.e. first to evaluate the Running Totals Last 12 Months and then also of last one year. I’ve used your measure in my PBIX file and you can cross-verify the results alongwith the results that I’ve evaluated with my measures.

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note’s:

1). In my working PBIX file, I’ve just changed the condition to Running Totals Last 3 Months rather than 12 months since my dataset is small but the logic remains the same in it’s entirety.

2). Refer the “Running Totals L3M - Check” page to cross-verify the results.

3). While posting your query, please include either the working or the example PBIX file for the reference of the forum members so that everyone can assist you in a better and efficient manner. When files are not included it consumes the time of the memebrs to create the files by themselves in order to provide the solutions.

Thanks and Warm Regards,
Harsh

Grouping and Segmenting - Harsh.pbix (613.1 KB)

1 Like
4

Thanks Harsh - spot on again! :grinning: