Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Day of Week (This Week) vs Day of Week Average (Previous 20 Weeks)

So I need to create a new measure that averages the last 20 weeks for that particular day. 

 

For example, I work for an Ambulance Company and on Mondays, we may average 50 transports a day. 

 

I want to set up a gauge that shows how many Transports we completed this week (the value) and then set the "Target Value" as the average with the "Maximum Value" being the "Target". 

 

Basically, I am wanting a day by day goal of the number of transports we are supposed to so the CEO and Operational leaders can see on a day by day basis if we are meeting or exceeding our average each day. 

 

If you have suggestions on a different visualization then I would appreciate that.  I am new to Power BI and want to make the most of it. 

 

I want to create a page/dashboard that shows each day. 

 

To summarize: 

 

I need 7 Gauges (One for each day of the week).  Each Gauge will represent a day.  On Sunday, I want the gauge to show the average number of transports on Sunday for the last 20 weeks.  The "needle" on the gauge will show "this week's Sunday" 

 

So here are the measures I need help with. 

 

sundayThisWeek (Shows the number of transports this week)

sundayPreviousAverage (Shows the average number of transports for the previous 20 weeks)

 

My data is being imported from a SQL server and has a date formatted (YYYY-MM-DD) as a string.  I have PowerBi auto convert that data into a date formatted as "dddd, MMMM d, yyyy" or (as an example, in human readable) "Wednesday, March 14, 2018"

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Here is a similar case for your reference.

https://community.powerbi.com/t5/Desktop/Monthly-Average-of-Previous-5-Years/m-p/435208#M200640

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Here is a similar case for your reference.

https://community.powerbi.com/t5/Desktop/Monthly-Average-of-Previous-5-Years/m-p/435208#M200640

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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