Forum Discussion
Helper IIDaily rolling average for total transactions
Hi,
I feel like I am very close, but can't figure out what I am doing wrong.
I have a table with all transactions, date, revenue per transaction. This is have I have for a new column:\
Calc_7daysrolling = CALCULATE( AVERAGEX('Patient Visits','Patient Visits'[R_Total_Revenue]), DATESINPERIOD('Patient Visits'[Date], LASTDATE('Patient Visits'[Date]),-7,DAY))
I have a feeling it has to do with the face I have muliple transactions per day. Any ideas?
Thanks in advance.
Hi jason435,
From your description, per day has multiple transactions, you want to do moving 7 days average for everyday total transactions, right?
Assume the sample data like below:
You can create a summarized table to aggregate total transactions per day, then create a calculated column to do moving 7 days average. For details, you can download attached pbix file.
Table = SUMMARIZE('Patient Visits','Patient Visits'[Date],"TotalRevenue",SUM('Patient Visits'[Patient_Revenue]))
Calc_7daysrolling = CALCULATE( AVERAGEX(ALLSELECTED('Table'),'Table'[TotalRevenue]), DATESINPERIOD('Table'[Date], LASTDATE('Table'[Date]),-7,DAY))
Best Regards,
Qiuyun Yu
3 Replies
InterkoubessSolution Sage
jason435Its is pretty simple transactional data.
Patient_Revenue
Date
Patient ID
Patient_Revenue
The rest isn't needed for this.
v-qiuyu-msftCommunity Support
Hi jason435,
From your description, per day has multiple transactions, you want to do moving 7 days average for everyday total transactions, right?
Assume the sample data like below:
You can create a summarized table to aggregate total transactions per day, then create a calculated column to do moving 7 days average. For details, you can download attached pbix file.
Table = SUMMARIZE('Patient Visits','Patient Visits'[Date],"TotalRevenue",SUM('Patient Visits'[Patient_Revenue]))
Calc_7daysrolling = CALCULATE( AVERAGEX(ALLSELECTED('Table'),'Table'[TotalRevenue]), DATESINPERIOD('Table'[Date], LASTDATE('Table'[Date]),-7,DAY))
Best Regards,
Qiuyun Yu