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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Imthedan
Helper II
Helper II

Per Day Average of Measure.

I have a measure that I would like to get Per Day average. I want to see how many conversions (my measure is [CAPPT]) happen over a given period of time on average.

 

It would be nice to create separate measures that are rolling X (7, 14, ect.) day averages. I'd also be nice to have a measure that interacts with the date slider I have, so at any given period I am looking at, I can see what the average per day of this measure is. I have a calendar look up, but the issue I am having is I have to have future dates on this calendar (to account for future appointments). This calendar is set 40 days in the future, so using code like MAX doesn't seem to work for me.

 

Can someone help me? I have LU_Calendar[Date] and [CAPPT]. 

2 REPLIES 2
Imthedan
Helper II
Helper II

@FreemanZ Thank you for the response.

 

Here is some extra data to help with the issue:

 

I have a measure that uses COUNTROWS to find how many appointments I have:

 

GAppointments = CALCULATE(COUNTROWS(2023),2023[source] = "g",2023[medium] = "cpc",2023[Appointment] = "Appointment")

 

I want to count this on a rolling average (or just last 7 days from yesterday). 

 

8/24/235
8/23/234
8/22/232
8/21/236
8/20/237
8/19/232
8/18/2310

 

I want to take this data and just get an average. In this case it would be 5.4 (36/7). I want this to roll and always be yesterday-7 or whatever. 

 

In normal terms, I just want to be able to see how many appointments were generated, on average, the past 7 days. I have a date calendar (Calendar[Date]) that has the date from this year (1/1/23) through 60 days in the future (10/24/23). 

 

If more data is needed let me know. It's just a little bit of sensitive data so I'd have to parse it quite a bit to share and was hoping to avoid that. 

 

Thank you!

EDIT: The table seems to be messed up and I am unsure why it removed the extra column.

FreemanZ
Super User
Super User

hi @Imthedan 

provide some sample data to help those who might help you. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors