Daily rolling average for total transactions

[jason435]

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. 

[v-qiuyu-msft · Accepted solution]

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

[Interkoubess]

Hi @jason435,

Can you show us a sample date with you desired result.

We will try to figure out.

Thx

[jason435]

Its is pretty simple transactional data.

Patient_Revenue

Date

Patient ID

Patient_Revenue

The rest isn't needed for this.