Average of Specific Day Over Time

Question

Hi! How would I calculate the average of all instances of X day of month? I have a metric with $X by day, and want to return the average of all 1st, 2nd, 3rd, 4th etc. of the months. Preferably rolling so if my calendar starts in 2024 then Oct 15th 2025 would return ((Oct 14th’24 + Oct 14th’25) / 2).Thank you!

Min Li · Answer

Hello ​@ljanowskiFirst, create a new column in Day dimension for Day # using this formula:Day # =Item(rank(Day,Day.Month),'Day #'.Name)Second, create a new metic for calculating the average value by day #Target Metric = ‘Source Metric’[BY:->Day.Month.'Month of Year'][BY Avg:Day.'Day #']Hope it helps.

Ask your question here!

Not finding what you're looking for?

Sign up

Single Sign-On Need help?

Log into the Pigment Community

Single Sign-On Need help?

Scanning file for viruses.

This file cannot be downloaded