Want to calculate average of last 14 weeks range

[bhuprakashs]

Hi team,

 I have a measure [% of Non Standardized Jobs] which I am using in table visual and showing last 14 weeks data based on YearMonth selection from slicer.
Now I want to calculate average of last 14 weeks value so that I can use as a benchmark to compare , if value is below average then make "Red" else if greater than average value then make "Green" . I have created below measure but not giving me correct average number. 

% of Non Standardized Jobs Average =

VAR WeeklyTable =

    ADDCOLUMNS (

        TOPN (

            14,

            SUMMARIZE ( VALUES ( 'Dates'[Week Start Date] ), 'Dates'[Week Start Date] ),

            'Dates'[Week Start Date], DESC

        ),

        "WeeklyValue",

            CALCULATE (

                [% of Non Standardized Jobs],

                REMOVEFILTERS ( 'Locations' ),

                REMOVEFILTERS ( 'Job Types' ),

                REMOVEFILTERS ( Dates ),

                REMOVEFILTERS ( 'Dynamic Table (Reporting Only)' )

            )

    )

RETURN

    AVERAGEX ( WeeklyTable, [WeeklyValue] )

51% is the average value which I want to preserve and want to use in my conditional formatting but it is changing weekly basis. I need same 51% in all weeks for last 14 weeks.

Year Week , I am using in table visual from the disconnected date table ('Dynamic Table (Reporting Only)') 

Please help,

[danextian · Accepted solution]

Hi @bhuprakashs 

It appears you already have  year-week column which is sorted chronologically. If you want to dynamically get the avege for the last 14 weeks dynamically and show that across all weeks, try this

ATR Last 14 Weeks = 
VAR WeekTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Dates ), Dates[Year-Week] ),
        "MaxDate", CALCULATE ( MAX ( Dates[Date] ) )
    )
VAR Last14Weeks =
    TOPN ( 14, WeekTable, [MaxDate], DESC )
VAR Weeks =
    SELECTCOLUMNS ( Last14Weeks, [Year-Week] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Year-Week] )
            IN Weeks || NOT ( HASONEVALUE ( Dates[Year-Week] ) ),
        AVERAGEX ( Weeks, [ATR] )
    )

Note: you can use ALL instead of ALLSELECTED within SUMMARIZE. ALLSELECTED will allow you to select a date element and will workback the 14 weeks from there.

Now, if you want to show the same value for all cells, you can use ALLSELECTED() which should consider all columns being added to the viz. It returns all filters visible in the current query context — across all columns in the filter context. 

ATR Last 14 Weeks ALLSELECTED = 
VAR WeekTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Dates ), Dates[Year-Week] ),
        "MaxDate", CALCULATE ( MAX ( Dates[Date] ) )
    )
VAR Last14Weeks =
    TOPN ( 14, WeekTable, [MaxDate], DESC )
VAR Weeks =
    SELECTCOLUMNS ( Last14Weeks, [Year-Week] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Year-Week] )
            IN Weeks || NOT ( HASONEVALUE ( Dates[Year-Week] ) ),
        CALCULATE ( AVERAGEX ( Weeks, [ATR] ), ALLSELECTED () )
    )

Please see the attached pbix.

[v-karpurapud · Accepted solution]

Hi @bhuprakashs 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @rohit1991 , @speedramps ,@danextian and @ABD128  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? This will help other community members solve similar problems faster.

Thank you.

[v-karpurapud · Accepted solution]

Hi @bhuprakashs 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @rohit1991 , @speedramps ,@danextian and @ABD128  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? This will help other community members solve similar problems faster.

Thank you.

[bhuprakashs · Accepted solution]

Thank you so much all for your time and support. Your responses were helpful and gave me valuable insights, although they didn’t fully meet my specific requirement. I truly appreciate your efforts and contributions.

I’ll be closing this thread now. Thanks again!

[ABD128]

Hi @bhuprakashs 

1. Create a measure that calculates the constant average for the last 14 weeks

This will calculate the average of the last 14 weeks based on connected Dates table, and ignore row context in the disconnected table.

% of Non Standardized Jobs - Last14WkAvg =

VAR MaxDate =

CALCULATE ( MAX ( 'Dates'[Date] ),

ALLSELECTED ( 'Dates' )

)

VAR Last14Weeks =

CALCU

LATETABLE (

TOPN (

14,

VALUES (

'Dates'[Week Start Date] ),

'Dates'[Week Start Date], DESC )

'Dates'[Date] <= MaxDate )

RETURN

AVERAGEX (

Last14Weeks,

CALCULATE ( [% of Non Standardized Jobs] )

2. Create a Week Flag to filter ONLY last 14 weeks in visual To limit your visual to only 14 weeks, create a calculated column or measure that you use in a visual filter.

Use this measure:

IsLast14Weeks =

VAR MaxDate =

CALCULATE (

MAX ( 'Dates'[Date] ),

ALLSELECTED ( 'Dates' ) )

VAR Last14Weeks =

CALCULATETABLE (

TOPN (

14, VALUES ( 'Dates'[Week Start Date] ),

'Dates'[Week Start Date], DESC ),

'Dates'[Date] <= MaxDate )

RETURN IF (

MAX (

'Dates'[Week Start Date] ) IN Last14Weeks, 1, 0 )

3. Use the average for conditional formatting Use this condition for font or background color formatting in Power BI:

Color for Conditional Formatting =

VAR Benchmark = [% of Non Standardized Jobs - Last14WkAvg]

RETURN

IF (

[% of Non Standardized Jobs] < Benchmark, "Red", "Green" )

To correctly set up the visual based on your requirements, begin by using the Year-Week field from the 'Dynamic Table (Reporting Only)' (the disconnected table) in your table visual. Next, add a slicer for Year-Month from the connected 'Dates' table, which allows users to filter the data range dynamically. Include the measure [% of Non Standardized Jobs - Last14WkAvg] in the visual to act as a constant benchmark, ensuring that the same average is displayed across all 14 weeks. Then, apply the IsLast14Weeks = 1 measure as a visual-level filter to restrict the table to show only the most recent 14 weeks based on the user's selection. Finally, use the conditional formatting measure to compare the weekly values against the benchmark and apply color coding green if the value is above the average and red if it’s below for clear and consistent visual interpretation.

[danextian · Accepted solution]

Hi @bhuprakashs 

It appears you already have  year-week column which is sorted chronologically. If you want to dynamically get the avege for the last 14 weeks dynamically and show that across all weeks, try this

ATR Last 14 Weeks = 
VAR WeekTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Dates ), Dates[Year-Week] ),
        "MaxDate", CALCULATE ( MAX ( Dates[Date] ) )
    )
VAR Last14Weeks =
    TOPN ( 14, WeekTable, [MaxDate], DESC )
VAR Weeks =
    SELECTCOLUMNS ( Last14Weeks, [Year-Week] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Year-Week] )
            IN Weeks || NOT ( HASONEVALUE ( Dates[Year-Week] ) ),
        AVERAGEX ( Weeks, [ATR] )
    )

Note: you can use ALL instead of ALLSELECTED within SUMMARIZE. ALLSELECTED will allow you to select a date element and will workback the 14 weeks from there.

Now, if you want to show the same value for all cells, you can use ALLSELECTED() which should consider all columns being added to the viz. It returns all filters visible in the current query context — across all columns in the filter context. 

ATR Last 14 Weeks ALLSELECTED = 
VAR WeekTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Dates ), Dates[Year-Week] ),
        "MaxDate", CALCULATE ( MAX ( Dates[Date] ) )
    )
VAR Last14Weeks =
    TOPN ( 14, WeekTable, [MaxDate], DESC )
VAR Weeks =
    SELECTCOLUMNS ( Last14Weeks, [Year-Week] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Year-Week] )
            IN Weeks || NOT ( HASONEVALUE ( Dates[Year-Week] ) ),
        CALCULATE ( AVERAGEX ( Weeks, [ATR] ), ALLSELECTED () )
    )

Please see the attached pbix.

[bhuprakashs]

Hi @danextian , thank you for your solution and it is giving me correct average number also but still there a issue that it is not showing me data for last 14 weeks , in fact it is showing for all the weeks available in the Disconnected table ( Note: Dynamic Table (Reporting Only) is the disconnected Date table name from which table I am using Year - Week column and filter last 14 weeks using Calculation Group) . 
And because of this , It's giving an error while using in conditional formatting (image below 😞

Could you please help restrict  result to 14 weeks only so that another visual can work ? Thanks in advance

Additionally I want to let you know that I am filter data based on Year Month from the Dates table which is not a disconnected table. 

[danextian]

Hi @bhuprakashs 

My solution doesn’t use a disconnected dates table, as that wasn’t mentioned in your original post. For clarity, it’s important to include key details like that going forward. I’ve attached the updated PBIX file for your reference.

[bhuprakashs]

@danextian , now your solution is giving result in a way that 
for week 48 - average is 76
for week 48 & 49 - average is 83
for week 48, 49 & 50 - average is 86 and so on.......

how can you keep dates between to week 9 to wee 48 (2024) 

[speedramps]

It is bad partice to build your own DAX date logic.
A team of novices always make mistakes for year end and leap years and do it inconistently from report to report.

Causing reports not to balance.

Always always always use a Calnedar table for date logic.

Build it and test it once and use it always on you reports.

They will then be consistent and well documented.

Try this ...

Create calendar table

Calendar = 
// create a calendar based on the dataset dates
CALENDARAUTO()

Add these calculated columns to the calendar table

Month (MMM YYYY) = 
FORMAT('Calendar'[Date],"mmm") & " " &
FORMAT('Calendar'[Date],"yyyy") 

Start of week = 'Calendar'[Date] - WEEKDAY('Calendar'[Date],2) + 1

/*
type 1 =   The week containing January 1 is the first week of the year, and is numbered week 1.
type 2  =  The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
*/

Week offset = 
VAR startofthisweek = TODAY() - WEEKDAY(TODAY(),2) + 1 
RETURN
DATEDIFF(
TODAY(), 'Calendar'[Start of week] , 
WEEK)

Create a 1:M relationship from calendar[date] to yourtable[date]

Create this DAX measure  (changing the table and field names to your own) 

Ave weekly sales over 14 weeks = 

// get the last week from the slicer
var slicerweek = MAX('Calendar'[Week offset])

// create a temp table of dates within the 14 week data range
var daterange = 
FILTER(All('Calendar'), 
'Calendar'[Week offset] <= slicerweek &&
'Calendar'[Week offset] >= slicerweek - 13 )

// get the total for the date range 
var rangetotal = 
CALCULATE(
    SUM(Yourdata[Sales]),
    daterange
)

RETURN
// the average = total / 14 weeks
DIVIDE(rangetotal,14)

I tested it and it works ok.
The total from Sept 2024 for 14 weeks = 25,678 
Average =  25,678 / 14 weeks
=  1834.14

Please click thumbs up because I have spent a lot of time to try to help.
Then click accept solution if it helps,  You can accept multiple solutions from helpers.

Please raise a new question rather than extend this one for any extra requests.
If you quote @speedramps anywhere in the text then I will receive a notification and will try help if I am available

If you need more help then please provide example data as a table (not a screen print).

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[rohit1991]

@bhuprakashs It looks like your measure recalculates the average per row instead of keeping it fixed across the last 14 weeks.

Could you please try below version

% of Non Standardized Jobs Average =
VAR Latest14Weeks =
    TOPN(
        14,
        VALUES('Dates'[Week Start Date]),
        'Dates'[Week Start Date], DESC
    )
VAR AvgValue =
    AVERAGEX(
        Latest14Weeks,
        CALCULATE(
            [% of Non Standardized Jobs],
            REMOVEFILTERS('Locations'),
            REMOVEFILTERS('Job Types'),
            REMOVEFILTERS('Dates'),
            REMOVEFILTERS('Dynamic Table (Reporting Only)')
        )
    )
RETURN
    AvgValue

This will return the same average (e.g. 51%) across all 14 weeks which is perfect for your conditional formatting use case.