Average percentage for the last 5 weeks by the days of the week

[Tash_247]

Hi, Guys

Please can you assist me.

I have been trying to calculate the Average percentage for the last 5 weeks by the days of the week .

So I need the average for the last 5 Mondays,  last 5 Tues, etc....

I have tried the following but not getting what i expect.

im getting the percentage divided by 5, and not the average for the last 5 weeks.

per_active_reg_5_week_avg =
VAR selectedWeekNum =
MAX ( Query1[Week of Year] )
VAR currentDayName =
FIRSTNONBLANK ( Query1[Day Name], 1 )
RETURN
(
(
CALCULATE (
( query1[per_active_reg]),
FILTER (
ALLEXCEPT ( Query1, Query1[trans_date].[Date]),
WEEKNUM ( Query1[trans_date])
>= selectedWeekNum - 5
&& WEEKNUM (Query1[trans_date] ) < selectedWeekNum
&& Query1[Day Name]= currentDayName
)
)
)
/ 5
)
+ 0

[v-shex-msft · Accepted solution]

HI @Tash_247,

You can try to use the following measure formula if it meets your requirement:

Measure =
VAR currDate =
    MAX ( 'Table'[trans_date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 35 )
VAR _startdate =
    prevDate
        + ( 1 - WEEKDAY ( prevDate, 2 ) ) + 7
VAR _enddate =
    currDate + 7
        - WEEKDAY ( currDate, 2 )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[per_active_reg] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [trans_date] >= _startdate
                && [trans_date] <= _enddate
        )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @Tash_247,

Since we not so clear for your data structure, can you please share some dummy data with the expected result to help us clarify your scenario and test to coding formula on it?

How to Get Your Question Answered Quickly  
Regards,

Xiaoxin Sheng

[Tash_247]

@v-shex-msft 

Please see excel data link below:

Sample of data 

Im also getting an error calculating the week calculation.

[v-shex-msft]

Hi @Tash_247,

I can't get the sample file from the shared link, it still requires additional permission for access. Can you please fix this?(notice: please not attach sensitive data in it)
Regards,

Xiaoxin Sheng

[Tash_247]

@v-shex-msft 

Hi, I hope these links below works:

data 

https://1drv.ms/x/s!Aj2m82PUTehbdLxYUDfRotVMCKE?e=ecti7j

[v-shex-msft · Accepted solution]

HI @Tash_247,

You can try to use the following measure formula if it meets your requirement:

Measure =
VAR currDate =
    MAX ( 'Table'[trans_date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 35 )
VAR _startdate =
    prevDate
        + ( 1 - WEEKDAY ( prevDate, 2 ) ) + 7
VAR _enddate =
    currDate + 7
        - WEEKDAY ( currDate, 2 )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[per_active_reg] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [trans_date] >= _startdate
                && [trans_date] <= _enddate
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Xiaoxin,

I have similar situation. I need to calculate past 6 week average. 

For example: Last 6 week Monday average, Last 6 week Tuesday average and so on. And I need to put last 6 week Monday average on coming Monday, Last 6 week Tuesday average on coming Tuesday and so on..

I have week started 0 as Sunday, 1 as Monday and so on.  

I have Date Column, Volume Column for average.

[Tash_247 · Accepted solution]

@v-shex-msft 

Thank you so much, it is correct.

I have tested a few senarios.

Your help is much appreciated.

[Tash_247]

@amitchandak @amitchandak 

I have attached the information, in previous post.

Please can you assist.

Thanks you.

[amitchandak]

@Tash_247 , have these few columns in your date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

and have a measure like

Last 5 Monday week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank] && 'Date'[Weekday] =2)))/5

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Tash_247]

@amitchandak 

Thanks for the information.

I have created the columns and measures.

Im getting an error with the measure.

Please also advise do i have to create this measure for everyday of the week ? 

As I require just 1 measure to calculate for everyday of the week.

Thank you.