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"
