Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate average by week base on a date

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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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]
)

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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]
)
  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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