How to display Average Age of a variable over time (time between fixed start date and today)

[Anonymous]

I have a dataset where each row represents an alarm and each has a particular start date.

I can easily calculate the current average age of my alarms by making a custom column = DATEDIFF(AlarmStartDate,today())  and show it in a Card visualization, summarizing as Average.

However, I'm also trying to display the average age of alarms over time as a line chart (where the x axis is time.) What I would need to do is calculate the average age of alarms on all past dates, which would require somehow using that day's date as the end date in DATEDIFF instead of using TODAY().

This picture is what I would like - I made this in a Google Sheet. The average age of alarms increases every day until a new alarm starts - that's where the average drops down.)

It's sort of a rolling average, but not exactly - only the end date 'rolls' and there are no periods. I also tried a MTD measure, but that ends up calculating only the average age of alarms for those with AlarmStartDates within the particular month on the X axis.

Is it possible to do this? Feel like I'm maybe missing something obvious. Thanks!

[Anonymous]

Hi @Anonymous ,

I need 1 clarification, Could you please make a rough line chart(in excel or paper) with the sample data that will be helpful to understand your scenario(Mainly X-axis).

Or as @Icey mentioned you can add pbix file too.

Best Regards,

Ravi

[Anonymous]

@Anonymous and @Icey  does this help?

Each bar represents the average of all alarms' age on that particular day. When the bars drop, that indicates that a new alarm just started that day, thus lowering the average. (Note: there are no alarms before 11/2017) 

(link to spreadsheet)

To make this chart in a spreadsheet:

1. Made a DateDim column from 1/1/2017-9/20/2019

2. Made a column for each alarm

3. Did a DATEDIFF calculation for each alarm on every row (start = AlarmStartDate, end = current row of DateDim), so that on the row that the alarm starts, there is a 1, the next day a 2, and so on

4. Made a column at the end that averages the whole row of datediff results, so that each row in my table has a number corresponding to the average age of all alarms on that particular date

5. Plotted the column of averages against the DateDim column

I'll be back to my pc next Monday and will be able to reply then. Thanks for the help!

[Anonymous]

It's Quite Clear now but Could you please allow write access to the spreadsheet:

my email: ravik4934@gmail.com

[Anonymous]

Hi @Anonymous, any progress on the calculation?

[Icey]

Hi @Anonymous ,

If you do not mind, please give me an example PBIX file.

Best Regards,

Icey

[Anonymous]

Sure thing @Anonymous , added in OP and here it is:

https://drive.google.com/open?id=1j62hqy44ft7C7eQuAp3CYoz__dpeE-Wh 

[Anonymous]

Hi @Anonymous 

Could you please attach a sample dataset. It will be helpful for test cases.

Best Regards,

Ravi