12 Weeks orders Average based on slicer selection

[PowerBI__]

Hello,
I have a table below:

Week Start Date	Price
3-Jun-24	65
10-Jun-24	65
17-Jun-24	87
24-Jun-24	7
1-Jul-24	97
8-Jul-24	635
15-Jul-24	97
22-Jul-24	7
29-Jul-24	365
5-Aug-24	97
12-Aug-24	987
19-Aug-24	685
26-Aug-24	897
2-Sep-24	5
9-Sep-24	78
16-Sep-24	858
23-Sep-24	8
30-Sep-24	58

Each row represents an order and price represents its price. I want to create a line graph which lets say if I select the slicer of date from range of 1st september till 30 September. It should show me the weekly average for orders and another measure for weekly average of price. In line graph lets say first week is 2 September then the value against it should be average of 2 september and previous 11 weeks and divided by 12. In the next week of 9th September in line graph the value should be 9th September+previous 11 weeks and divided by 12

[v-jtian-msft]

Hi,Selva-Salimi ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@PowerBI__ .I am glad to help you.

I hope my test below meets your expectations
For each week's data, dynamically display the average of the sales for the first 12 weeks of the corresponding week based on the minimum filtered value of the slicer (first summing, then directly dividing by 12)

The calculation logic I've written will only calculate the average of sales over the 12 weeks for a time span of 12 weeks or more. The current row of data for less than 12 weeks will be displayed as 0 (the existing minimum time to the current row of the date and time of the span of weeks is less than or equal to 11 weeks).

You need to modify the measure according to your actual needs. Or write a measure that meets your real computing environment.

Here is my test measure:

Avg_12weeks = 
VAR _minDate =
    MIN ( 'CalendarDate'[Date] )
VAR _startDay =
    CALCULATE (
        MIN ( 'Table'[Week Start Date] ),
        FILTER ( 'Table', 'Table'[Week Start Date] >= _minDate )
    )
VAR _selectedweekNum =
    CALCULATE (
        MIN ( 'Table'[WeekNum] ),
        FILTER ( 'Table', 'Table'[Week Start Date] = _startDay )
    )
VAR _startweekNum = _selectedweekNum - 11
VAR _sumSales =
    CALCULATE (
        SUM ( 'Table'[Price] ),
        FILTER (
            ALL ( 'Table' ),
            [WeekNum] >= _startweekNum
                && [WeekNum] <= _selectedweekNum
        )
    )
RETURN
    IF (
        CALCULATE (
            MIN ( 'Table'[WeekNum] ),
            FILTER ( ALL ( 'Table' ), [WeekNum] = _startweekNum )
        )
            = BLANK (),
        0,
        DIVIDE ( _sumSales, 12 )
    )
    // The calculation logic I've written will only calculate the average of sales over the 12 weeks for a time span of 12 weeks or more. The current row of data for less than 12 weeks will be displayed as 0 (the existing minimum time to the current row of the date and time of the span of weeks is less than or equal to 11 weeks).

Here is my test data:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBI__]

Hello @v-jtian-msft ,
The issue I am getting is on count of orders. It is working on sum fine but not on count lets say if I want to take average order count I received in last 12 weeks

[Ashish_Mathur]

Hi,

This measure pattern should work

Total orders = counta(Data[Order number[)

Total order in 12 weeks ending = calculate([Total orders],datesbetween(calendar[date],min(calendar[date])-77,min(calendar[date])+7))

Hope this helps.

[v-jtian-msft]

Hi,@PowerBI__ .Thank you for your reply.
You can show your current problematic dax code, please share it as a pbix file on the forums and provide the corresponding screenshot of the code that doesn't contain sensitive data, this will help to help you quickly identify possible problems with the code.
URL:
How to Get Your Question Answered Quickly - Microsoft Fabric Community

[Selva-Salimi]

Hi @PowerBI__ 

you can follows these steps:

1. wite a column to have an order for weeks. this column could be written as follows:

    week_orders = calculate( count(week_start) , filter(your_table, week_start <= earlier (week_start))

2. write a measure using the previous column as follows:

    measure Avg_12M = calulate(sum(price) , filter (all(your_table) , week_order <= selectedvalue(week_order)-12 )) / 12

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.