I'm trying to calculate the median of the all user's total over the last 7 days. The data is structured on a daily basis and each user can have mutliple amounts in a given day. I would like to SUM each user's total over a rolling 7 day period and then find the median amount of the total. I'm using MEDIANX and SUMMARIZE which will calculate the MEDIAN correctly if used in a card, but when I try to use the measure in a table, it's only calculating the median for a single day. Below are my formulas.
User Total = SUM(Users[Amount])
User Total L7d =
CALCULATE(
[User Total],
DATESINPERIOD ( Users[Date], LASTDATE(Users[Date]), -7, DAY )
)
Median Total L7d =
MEDIANX (
SUMMARIZE (
Users,
Users[UserID],
"Median Total L7d", Users[Users Total L7d]
),
[Median Total L7d]
)
The formula "User Total L7d" is calculating the rolling sum correctly in the table, but when I add the "Median Total L7d" to the table (by date) it only calculates the median for that day. Sorry I can't post any screenshots, the data is confidential. Please let me know if anything is confusing and I can provide more detail.
