Skip to main content
cancel
Showing results for
Search instead 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 III

Moving Average of length of stay

I have a table of data that looks like this:

 Location User ID Move In Date Move Out Date Days Stayed 1 32 3/9/2019 3/26/2019 17 3 22 3/9/2021 1/4/2022 300 3 1233 3/12/2024 5/3/2024 52 1 12132 3/31/2020 10/24/2022 937 1 123242 10/21/2019 11/22/2021 763 3 65346 11/21/2014 12/4/2014 12

What I would like to have is what is the average length of stay for each location from todays day, 365 days back.  I can get it for today going back, but what I need help with is how do I do: What is the for the last 365 days as of the end of each month going back for 3 years in a line graph?  I would then want the last month to drop off as as the next one is added.

4 REPLIES 4
Helper III

I did the two new measures, but I am failing to see how I can use them to get a line graph to show a data point for each month.

Super User

Hi @petermb72 -Hope in your model date table is there.

Create a measure to calculate the average length of stay for each location

Average Length of Stay Last 365 Days =
VAR StartDate = TODAY() - 365
RETURN
AVERAGEX(
FILTER(
'YourDataTable',
'YourDataTable'[Move In Date] >= StartDate &&
'YourDataTable'[Move Out Date] <= TODAY()
),
'YourDataTable'[Days Stayed]
)

Create another measure Dynamically for Average Stay by Month End

Average Length of Stay by Month End =
VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
VAR StartDate = CurrentMonthEnd - 365
RETURN
AVERAGEX(
FILTER(
'YourDataTable',
'YourDataTable'[Move In Date] >= StartDate &&
'YourDataTable'[Move Out Date] <= CurrentMonthEnd
),
'YourDataTable'[Days Stayed]
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

How do I use this in a line graph?

Helper III

thank you by the way!

Helpful resources

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.

Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors