calculate average daily of last week

[rezaaditia]

Hi –

I believe this will be easy for you guys, but its quite difficult for me…J

Anyway, I have big data , inside that big data, I have 2 columns for:

• Validate source => filled with (inflow, outflow, backlog and false)
• Weeknum

Based on this 2 column, can you please share how to calculate average daily of last week (Monday to Friday) of inflow, outflow and backlog

Thanks

[RobbeVL]

Hi There,

To have a fast and easy answer to this question, please provide some sample data.

This will be easier for you and easier for people creating your solution.

Robbe

[rezaaditia]

Hi -

sorry, was busy with my works.

please see sample file in gdrive... 

https://drive.google.com/open?id=1AQoHnI-hoysQNTKJtuI8iPBZYoN9FXiH

in this sample pbix, i've created weekly trend of previous weeks,

now based on this i want to get daily average of last week.

hope this is sample is enough.

thanks

[RobbeVL]

If I understand your request correctly, this is What I added:

first I added a Column With the Day of the week: 

Weekday = WEEKDAY(Sheet1[validate_date];2)

Then I calculated the average for weekdays:

avg = CALCULATE(COUNT(Sheet1[key]), Sheet1[Weekday] <6, Sheet1[Week]= WEEKNUM(TODAY())-1) /5

Now you have a measure that calculates The average count per day for last weeks workdays.

Hope this helps!

[rezaaditia]

Hi @RobbeVL 

thanks for your response, but your formula is not as per my expecation (please see chart #2 for result of using your formula given). 

Might be i am not giving you clear request.

in first chart, it show total of inflow and outflow of the week (for monday and friday).

now using same chart, i want to have average of total inflow and outflow per week.

below if i create in excel

T

Thanks

[rezaaditia · Accepted solution]

Hi -

finally found the answer 

Measure3 = (CALCULATE(COUNT(Sheet1[validate source3])))/(CALCULATE(DISTINCTCOUNT(Sheet1[validate_date].[Date])~FILTER(Sheet1~Sheet1[Week])))