cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Rolling Daily Average Resetting at Beginning of Each Month

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

2 ACCEPTED SOLUTIONS
Super User

@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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Super User

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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

9 REPLIES 9
Super User

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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Helper I

@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

Super User

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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Helper I

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.

Helper I

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!!!!

Super User

@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

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Helper I

Thank you!

Helper I

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

Super User

Check table by going to model not visual

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors