Rolling Daily Average Resetting at Beginning of Each Month

[kc_]

Hello All 

Below is what I am trying to achieve in Power BI:

In essence I need to be able to create a daily rolling average which resets at the beginning of each new month....

Thanks in advance!

[bhanu_gautam · Accepted solution]

@kc_ ,Create a new calculated column using below measure

Rolling Average =

VAR CurrentDate = 'Table (2)'[Date]

VAR CurrentMonth = MONTH(CurrentDate)

VAR CurrentYear = YEAR(CurrentDate)

RETURN

    CALCULATE(

        AVERAGE('Table (2)'[Job Completed per dat]),

        FILTER(

            'Table (2)',

            YEAR('Table (2)'[Date]) = CurrentYear &&

            MONTH('Table (2)'[Date]) = CurrentMonth &&

            'Table (2)'[Date] <= CurrentDate

        )

    )

Attaching PBIX with solution

[Joe_Barry · Accepted solution]

Hi @kc_ 

Are you usinga date table in your model? Then this will help

Avg. Jobs Per Day = 
VAR LastCurrentDate =
    MAX ( 'DIM Date'[Date] )
VAR Period =
    DATESBETWEEN ( 'DIM Date'[Date], EOMONTH(LastCurrentDate, -1) + 1, LastCurrentDate)
VAR TotalJobs =
    CALCULATE (
        SUM(Jobs[Jobs Completed per Day]),
        Period
    )
VAR NumOfDays =
    COUNTROWS(Period)
VAR Result =
    DIVIDE(TotalJobs, NumOfDays)
RETURN
    Result

Joe

[kc_]

@Joe_Barry hoping you can provide some further assistance...

As mentioned before the DAX you provided works, see table below:

However, for each date is a working day allocated to it (within the data set) and when I incorporate this within the table the following occurs:

In essence I am looking to achieve something similar to the below:

Are you able to assist....

Many thanks

[Joe_Barry]

Hi @kc_ 

Please try for no Staurdays or Sundays

Avg. Jobs Per Day No Weekends = 
VAR LastCurrentDate =
    MAX ( 'DIM Date'[Date] )
VAR Period =
    DATESBETWEEN ( 'DIM Date'[Date], EOMONTH(LastCurrentDate, -1) + 1, LastCurrentDate)
VAR WeekdaysPeriod =
    FILTER (
        Period,
        WEEKDAY ( 'DIM Date'[Date], 2 ) < 6
    )
VAR TotalJobs =
    CALCULATE (
        SUM(Jobs[Jobs Completed per Day]),
        WeekdaysPeriod
    )
VAR NumOfDays =
    COUNTROWS(WeekdaysPeriod)
VAR Result =
    DIVIDE(TotalJobs, NumOfDays)
RETURN
    Result

This one will exclude days where no Jobs happen

Avg. Jobs Per Day - Leave out non worked days= 
VAR LastCurrentDate =
    MAX ( 'DIM Date'[Date] )
VAR Period =
    DATESBETWEEN ( 'DIM Date'[Date], EOMONTH(LastCurrentDate, -1) + 1, LastCurrentDate)
VAR NonZeroJobsPeriod =
    FILTER (
        Period,
        CALCULATE ( SUM ( Jobs[Jobs Completed per Day] ) ) > 0
    )
VAR TotalJobs =
    CALCULATE (
        SUM(Jobs[Jobs Completed per Day]),
        NonZeroJobsPeriod
    )
VAR NumOfDays =
    COUNTROWS(NonZeroJobsPeriod)
VAR Result =
    DIVIDE(TotalJobs, NumOfDays)
RETURN
    Result

Joe

[kc_]

Thanks for this @Joe_Barry , however, something is not working for me...perhaps you can send accross your PBIX file instead.

Also, it is critical that the working day numbers are shown as opposed to the actual days of the week since this is what the company reports on. 

Below is a screenshot of what I am trying to achieve.

[kc_]

Thanks for this Joe, yes I do have a Date Table, however...see below:

The average jobs per day on 2.01.2024 needs to be 86 and not 43. I only need an average to be calculated if a job has been completed on any given day. Also for context, jobs are only completed Monday to Friday.

The workaround therefore - instead of pointing to the date table, I am pointing to the date completed field of the source data table, in doing so, I am getting the expected figures.

This has worked a treat! Many thanks!!!!

[bhanu_gautam · Accepted solution]

@kc_ ,Create a new calculated column using below measure

Rolling Average =

VAR CurrentDate = 'Table (2)'[Date]

VAR CurrentMonth = MONTH(CurrentDate)

VAR CurrentYear = YEAR(CurrentDate)

RETURN

    CALCULATE(

        AVERAGE('Table (2)'[Job Completed per dat]),

        FILTER(

            'Table (2)',

            YEAR('Table (2)'[Date]) = CurrentYear &&

            MONTH('Table (2)'[Date]) = CurrentMonth &&

            'Table (2)'[Date] <= CurrentDate

        )

    )

Attaching PBIX with solution

[kc_]

Thank you!

[kc_]

Thank you Bhanu - however, would you mind re-attaching the PBIX file please...

[bhanu_gautam]

Check table by going to model not visual