Calculate average by week base on a date

[Anonymous]

Hi everybody,

I am trying to calculate an average by week based on a date.
My data table is about alerts from servers

Some of these servers have been patched, and for theses one I have a patch date

The columns of my table are :
- the server name
- the date when the alert occurs
- the description of the alert

- the patch date

Base on the patch date I created :
- a calculated column to know if the alert occurs before or after the patch date (based on the alert date)
- a calculated column containing the week number of the alert (based on the alert date)

I would to calculate an average of alert by week, before and after the pach date (in order to compare the patch efficience).

But nothing better than an example 😊

Example given :
I have 100 alerts for a server X :
week 1 : 15 alerts
week 2 : 40 alerts
week 3 : 35 alerts
-- patch --
week 4 : 8 alerts
week 5 : 2 alerts

I would like to be able to report something like :
Before the pach I had an average of 30 alerts by week, after the patch I have an average of 5 alerts by week

Thanks for your help

Bruno

[Anonymous]

Hello,

It works perfectly fine 😁

In a first way I had some trouble to apply the formula because I thought it was to be used as a calculated column, and I had the value 1 for all lines of my table.
When I used it as a measure the result is OK.

Actually I thouth to another way to manage with my problem, a lot more messy :

- I created measure to count the number of week before and after the patch date :

Number of weeks before patch =

CALCULATE(

COUNTROWS(

DISTINCT(Alerts[WeekNumber])

),

FILTER(

Alerts,

Alerts[BeforePatch] = 1

)

)

 Number of weeks after patch =

CALCULATE(

COUNTROWS(

DISTINCT(Alerts[WeekNumber])

),

FILTER(

Alerts,

Alerts[AfterPatch] = 1

)

)

- Measures to calculate the number of aleres before and afer patch

Number of alerts before patch =

CALCULATE(

COUNTROWS(Alerts),

FILTER(

Alerts,

Alerts[BeforePatch] = 1

)

)

Number of alerts after patch =

CALCULATE(

COUNTROWS(Alerts),

FILTER(

Alerts,

Alerts[AfterPatch] = 1

)

)

- and finaly 2 others measures to get the average

Average by week before patch = DIVIDE([Number of alerts before patch], [Number of weeks before patch], 0)

Average by week after patch = DIVIDE([Number of alerts after patch], [Number of weeks after patch], 0)

The result is the same but I gonna do it your way : the method is much more professionnal 👍

Thank you for you time

Sorry for the delay of the feed back

Best regards

[Anonymous]

@AlexisOlson and @v-henryk-mstf 

Hi,

Sorry for the delay : I didn't have the opportunoty to work on this report until today.
I will try asap and let you know how it works

[v-henryk-mstf]

Hi @Anonymous ,

I think the formula @AlexisOlson  provided is worthwhile. Please let me know the results of the test, and point out if the problem persists. If the problem has been solved, please mark the correct response as the standard answer to help the other members find it more quickly.

Looking forward to your reply.

Best Regards,
Henry

[AlexisOlson · Accepted solution]

Something like this?

WeeklyAvgBeforePatch =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( Alerts[WeekNumber] ),
        "@BeforeCount", CALCULATE ( COUNTROWS ( Alerts ), Alerts[BeforePatch] = 1 )
    ),
    [@BeforeCount]
)

WeeklyAvgAfterPatch =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( Alerts[WeekNumber] ),
        "@AfterCount", CALCULATE ( COUNTROWS ( Alerts ), Alerts[BeforePatch] = 0 )
    ),
    [@AfterCount]
)