Calculating Averages

[lamlamm24]

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_Demand_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. 

@amitchandak , @Idrissshatila , @Daniel29195 

[v-binbinyu-msft]

Hi @lamlamm24 ,

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.

[lamlamm24]

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.