Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculating Averages

I have the following

 

The image above is showing the average number of demand for the last 6 weeks and here is the DAX i used. 

 
AverageDemanLast6Weeks =
VAR StartDate = TODAY() - 42
VAR EndDate = TODAY()
RETURN
AVERAGEX(
    FILTER(
        'Date',
        'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate
    ),
    [Total_Rate]
 
Here is the CHALLENGE i am having now.
I want a situation where I pick a date within the NEXT 6 weeks for example 03/11/2024 (That happens to be a Monday).
When i select this DATE i want to see the average of the demand that occurred in the Last 6 Mondays.  
This is like I am using the average in the last 6 Mondays to predict what to expect for the next selected Monday.
Simply saying when I select a day in the future, show me the result of what has happened on this day in the Last 6 weeks. 
 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try below steps:

    1. Define Your Selected Future Date: Since you mentioned selecting a future date, ensure this date is captured in your model. For demonstration purposes, let's assume you have a way to select or input this date into Power BI, and we'll refer to it as 'SelectedFutureDate'.

    2. Calculate the Last 6 Mondays: We'll use a combination of DAX functions to filter the last 6 occurrences of Monday before the 'SelectedFutureDate'.

    Here's a DAX measure that should help:

    AverageDemandLast6Mondays = 
VAR SelectedFutureDate = DATE(2024, 3, 11) -- Replace this with your method of capturing the selected date
VAR TargetWeekday = 2 -- Monday is represented as 2 in DAX (1 = Sunday, 2 = Monday, etc.)
VAR RelevantDates = 
    FILTER(
        ALL('Date'), 
        'Date'[Date] < SelectedFutureDate 
        && WEEKDAY('Date'[Date], 2) = TargetWeekday
    )
VAR Last6Mondays = 
    TOPN(
        6, 
        RelevantDates, 
        'Date'[Date], 
        DESC
    )
RETURN
AVERAGEX(
    Last6Mondays, 
    [Total_Demand_Rate]
)

    Key Points in the Measure:

    - 'SelectedFutureDate': This should be dynamically set based on your selection mechanism in Power BI.
    - 'TargetWeekday': This is set to 2 to focus on Mondays. Adjust this value for other weekdays.
    - 'RelevantDates': Filters all dates to only include Mondays before the 'SelectedFutureDate'.
    - 'Last6Mondays': Uses 'TOPN' to get the last 6 Mondays from the 'RelevantDates' table.
    - The final 'AVERAGEX' calculates the average demand rate over these dates.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi I used Monday as a reference. 
      I want to be able to select any day of the week in the next 6 weeks for instance and get the value of the average for the last 6 weeks. What this means is that if i pick the date of next week wednesday (03/06/2024), what I expect to see in my Matrix Visual are the values for the averages for the last 6 wednesdays. Likewise, if i select next thursday, I want to see the average demands that occured in the last 6 Thursdays.