Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Average 4,8 weeks with Data Table contain weekly numbers

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? 

 

Need Help

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous - 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Ashish - hope below is helping:

      WE_FridaySubs_This_WeekSubs_Average_4_Weeks_Run_RateDesired Results
      WE 2024-09-20145513781519.75
      WE 2024-09-27144614201554.5
      WE 2024-10-04171514711587.75
      WE 2024-10-11146315521507.75
      WE 2024-10-1815941520 
      WE 2024-10-2515791555 
      WE 2024-11-0113951588 
      WE 2024-11-0814931508 
      WE 2024-11-15165  


      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]
          )