Displaying 6 Months back after selecting a month on slicer for a running total

[random11]

anyone able to help me.

for example if i had a sales of £100 per month for 12 months i want this to be cumulative so if i picked on a slicer December it would display £1200 and also the 6 previous months with what there totals would of acumulated in there month so downwards 1200,1100,1000,900 ect.

[Anonymous · Accepted solution]

Hi, @random11 

Based on your description, I created the following sample data:

First, I created the following table using parameters:

Next, I created a measure with the following DAX:

6 Month sales =
VAR _seleted_month =
    SELECTEDVALUE ( 'Month'[Month] )
VAR _number =
    CALCULATE (
        MAX ( 'Month'[Month] ),
        FILTER (
            ALL ( 'Month' ),
            'Month'[Month] = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) )
        )
    )
VAR _past_6_month_sales =
    CALCULATE (
        SUM ( 'Table'[sales] ) * _number,
        FILTER (
            'Table',
            MONTH ( 'Table'[Date] ) <= _seleted_month
                && MONTH ( 'Table'[Date] ) >= _seleted_month - 6
        )
    )
RETURN
    IF (
        ISBLANK ( _seleted_month ),
        SUM ( 'Table'[sales] ) * _number,
        _past_6_month_sales
    )

The table visual is set as follows:

Here are the results:

When the slicer was not selected, I did a processing and now all the data. When the slicer is selected, the results you expect are displayed. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Cumulative Sales Last 6 Months = 
VAR MaxVisibleDate = MAX('Date'[Date])
VAR MinVisibleDate = EDATE(MaxVisibleDate, -6)

RETURN
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MaxVisibleDate &&
        'Date'[Date] > MinVisibleDate
    )
)