Average 4,8 weeks with Data Table contain weekly numbers

[jhauw74]

Hi Experts,

I need to calculate the average 4 weeks and 8 weeks from a table that contains an aggregate weekly values.

This table connects to calendar table using column that contains daily dates. 

The NumOrders is what I wants to calculate - however PeriodDate is containing dates for WE 8th Nov.

Below is the snapshot:

Below is the total of weekly results:

I am using this DAX :

Subs_Average_4_Weeks_Run_Rate =

VAR _current_week =

    MAX('BOM_DIM_Date'[Curr_Prev_Week_Count])

VAR _start_week =

    _current_week - 4

RETURN

    AVERAGEX(

        FILTER(

            ALL('BOM_DIM_Date'),

            'BOM_DIM_Date'[Curr_Prev_Week_Count] >= _start_week &&

            'BOM_DIM_Date'[Curr_Prev_Week_Count] < _current_week

        ),

        [Submission_Order_Fixed_5G_Starlink]

    )
However this resulting in incorrect results:

I think the issue is because the source table contains weekly dates vs daily dates and causing the calculation become wrong.

Is there a way to calculate this in DAX? 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[jhauw74]

Hi Ashish - hope below is helping:

WE_Friday	Subs_This_Week	Subs_Average_4_Weeks_Run_Rate	Desired Results
WE 2024-09-20	1455	1378	1519.75
WE 2024-09-27	1446	1420	1554.5
WE 2024-10-04	1715	1471	1587.75
WE 2024-10-11	1463	1552	1507.75
WE 2024-10-18	1594	1520
WE 2024-10-25	1579	1555
WE 2024-11-01	1395	1588
WE 2024-11-08	1493	1508
WE 2024-11-15	165

As I have mentioned the table orders have an aggregate weekly values and that connects to the calendar table that have daily dates.

I managed to create another calendar table that contains only week ending date only however the results does not seem to work as desired above.

Here's the DAX :

Subs_Average_4_Weeks_Run_Rate =

VAR _current_week =

    MAX('BOM_DIM_Week'[Curr_Prev_Week_Count]) --12

VAR _start_week =

    _current_week - 4 --8

RETURN

    AVERAGEX(

        FILTER(

            ALL('BOM_DIM_Week'),

            'BOM_DIM_Week'[Curr_Prev_Week_Count] >= _start_week &&

            'BOM_DIM_Week'[Curr_Prev_Week_Count] < _current_week

        ),

        [Submission_Order_Fixed_5G_Starlink]

    )

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[rajendraongole1]

Hi @jhauw74 - Create a updated DAX measure as below:

Subs_Average_4_Weeks_Run_Rate =
AVERAGEX(
DATESINPERIOD(
'BOM_DIM_Date'[PeriodDate], -- Use the weekly period end date
MAX('BOM_DIM_Date'[PeriodDate]),
-4,
WEEK
),
[Submission_Order_Fixed_5G_Starlink]
)

Using DATESINPERIOD instead of manually filtering by week count is generally more reliable for time-based calculations in Power BI and should give you the correct 4-week average even with weekly aggregated data.

[jhauw74]

Hi Raj - thank you for helping - I copied the DAX :

But it error out for the WEEK part - the Week ending date is just a normal date but only contains the Friday's date for the week.