Showing Last 03 Month Average Sales Rolling

[ADSL]

Hi BI Community Team,

As per purpose, we want to see and track only top 10 customers only that average purchased at least 03 month with high amount. Then I have search and found this dax:

Top10 = CALCULATE([Total Sales], KEEPFILTERS((TOPN(10, ALLSELECTED(Customers[Customer Name]), [Total Sales], DESC))))

It's working and showing the month that we stand only. If we click "November" then result is nothing.

Any suggestion to show the Top10 customers with last 03 month average sales rolling?

Thanks and Regards,

[v-zhengdxu-msft]

Hi @ADSL 

Could you please provide raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

[ADSL]

Hi @v-zhengdxu-msft ,

Please kindly assist to check and advise of this.

Actually we want to track the Top customers (10) from rolling as per average last 03 month, how many sales amount for these customers if we're standing in the new month.

Result Expectation: 

[v-zhengdxu-msft · Accepted solution]

Hi @ADSL 

Please try this:

Here I create a Measure:

MEASURE =
VAR _Slicer_Month =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR _Slicer_Year =
    YEAR ( MAX ( 'Calendar'[Date] ) )
VAR __Month =
    SUMMARIZE (
        SELECTCOLUMNS (
            CALENDAR (
                DATE ( _Slicer_Year, _Slicer_Month - 3, 1 ),
                DATE ( _Slicer_Year, _Slicer_Month - 1, 1 )
            ),
            "_Month", FORMAT ( [Date], "YYYY-mm" )
        ),
        [_Month]
    )
VAR _vtable =
    SUMMARIZE (
        ALL ( 'Sales Order' ),
        Customers[Customer Name],
        'Sales Order'[Month],
        "_SUMSales", SUM ( 'Sales Order'[Sales_Amt] )
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        'Customers'[Customer Name],
        "_AVG",
            VAR _currentName =
                SELECTEDVALUE ( Customers[Customer Name] )
            RETURN
                AVERAGEX (
                    FILTER ( _vtable, [Month] IN __Month && [Customer Name] = _currentName ),
                    [_SUMSales]
                )
    )
RETURN
    IF (
        SELECTEDVALUE ( Customers[Customer Name] )
            IN SELECTCOLUMNS ( TOPN ( 10, _vtable2, [_AVG], DESC ), [Customer Name] ),
        SUM ( 'Sales Order'[Sales_Amt] )
    )

Add it to the table visual, the result is as follow:

Best Regards

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

[ADSL]

Hi @v-zhengdxu-msft ,

Just want to clarify your measure, is it included to calculate average as well of the month that we stand? Example: We choose slicer of month "Aug" then average is calculated from "Jun - Aug" or "Jun-Jul"?

Please kindly feedback of this.

[v-zhengdxu-msft]

Hi @ADSL 

If you select August, the average is calcualated from May-July, and if you select September, the average is from June-August.

Here you can see the month range in the dax:

Best Regards

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

[ADSL]

Thank you for your helpful feedback and support of this post.

[dlopesc04]

That measure looks threatening

[Greg_Deckler]

@ADSL Better Rolling Average - Microsoft Fabric Community

[dlopesc04]

 Assuming you have a calendar table related to your fact table:

Top10 = 

var ReferenceDate = MAX(Calendar[Date])

var PreviousDate = DATESINPERIOD(Calendar[Date], ReferenceDate, -3, MONTH)

var Result = 

CALCULATE([Total Sales],

REMOVEFILTERS(Calendar),

KEEPFILTERS(PreviousDate),

KEEPFILTERS((TOPN(10, ALLSELECTED(Customers[Customer Name]), [Total Sales], DESC))))

RETURN
Result

Didn't test this at all, just plugged you measure into a logic that filters only the 3 last months based on slicer selection, I'm assuming your measure is already doing the "average sales rolling" part.

[ADSL]

Hi @dlopesc04 ,

I have tried and followed your advise but it's not working. Could you please assist to check with my sample data link https://drive.google.com/drive/folders/17bdbmVx8PA9rnt-8XCVlmeT3nEmUD0fZ?usp=drive_link and result expectation that mention above?

[dlopesc04]

Here you go buddy:

Top102_rolling = 

var ReferenceDate = MAX(Calendar[Date])
var PreviousDate = DATESINPERIOD(Calendar[Date], ReferenceDate, -3, MONTH)


var Result = 
CALCULATE([Total Sales],
REMOVEFILTERS('Calendar'),
KEEPFILTERS(PreviousDate),
KEEPFILTERS((TOPN(10, ALLSELECTED(Customers[Customer Name]), [Total Sales], DESC))))/CALCULATE(DISTINCTCOUNT('Sales Order'[Month]),REMOVEFILTERS('Calendar'),KEEPFILTERS(PreviousDate))

RETURN
Result

Basically looks like this:

I still haven't figured out how to attach a pbix file so here is a drive link for the pbix, enjoy:

https://drive.google.com/file/d/1rS9aFsykRnnCmz4lkZ_XWWDsgdLrFdqm/view?usp=sharing

[ADSL]

Hi @dlopesc04 ,

Thank you for your feedback. As per my expectation,

1. customer name will show only top 10 that calculate from average sales from last 03 month

2. Total Sales value will show the month that select of slicer.

Any suggestion/advice?

[dlopesc04]

@ADSL 

Any suggestion to show the Top10 customers with last 03 month average sales rolling?

---

What you wanted has already been achieved in the pbix I've sent.

2. Total Sales value will show the month that select of slicer.

---

Maybe I didn't understand fully, but if you want to show nothing on total rows you can do this:

Top102_rolling = 

var ReferenceDate = MAX(Calendar[Date])
var PreviousDate = DATESINPERIOD(Calendar[Date], ReferenceDate, -3, MONTH)


var Result = 
CALCULATE([Total Sales],
REMOVEFILTERS('Calendar'),
KEEPFILTERS(PreviousDate),
KEEPFILTERS((TOPN(10, ALLSELECTED(Customers[Customer Name]), [Total Sales], DESC))))/CALCULATE(DISTINCTCOUNT('Sales Order'[Month]),REMOVEFILTERS('Calendar'),KEEPFILTERS(PreviousDate))

RETURN
IF(ISINSCOPE(Customers[Customer Name]),Result,BLANK())

[ADSL]

Thank you for your feedback.