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