How to calculate rolling average where the average value is the count of dates

[AmritaOS]

Hello,

i am looking to calculate the rolling 7 day average.

my data consists of a number of duplicate dates which represents the number of contacts per date - so each time there is a contact on teh same date a duplicate date is added to the column.

i need to claculaye the number of contacts per date and then calculate the rolling 7 day average per date.

in my line chart i want to show the count per date and the 7 day rolling average per date.

this is what i have done - 

INST x Day Moving Average =

var sum_in_period = CALCULATE([Count Daily Instruction by Date],DATESINPERIOD(rInstructionAppointmentOpenedPBI[InstructionOpenedDate],LASTDATE(rInstructionAppointmentOpenedPBI[InstructionOpenedDate]),-7,DAY))

var days_in_period = CALCULATE(DISTINCTCOUNT(rInstructionAppointmentOpenedPBI[InstructionOpenedDate]), DATESINPERIOD(rInstructionAppointmentOpenedPBI[InstructionOpenedDate],LASTDATE(rInstructionAppointmentOpenedPBI[InstructionOpenedDate]),-7,DAY))

return

sum_in_period/days_in_period

it is giving me some values but when i calculate manually they are incorrect.

thanks,
Amrita

[Anonymous · Accepted solution]

Hi @AmritaOS 

Before you do anything in DAX and PBI, I'd strongly suggest you read this first. Your problem most likely stems from the fact that you are using the time-intel functions incorrectly. Please refer to the documentation (https:/dax.guide/datesinperiod) to see what conditions need to be satisfied to use the time-intel functions with confidence. Especially, read the Notes section.

[AmritaOS]

thank you this is perfect.

i have figured where i was going wrong.

[Jihwan_Kim]

Hi, @AmritaOS 

Please correct me if I wrongly understood your question.

If the above calculation gave other than your expectation, then please check if there were any zero-count-day within the 7 days, or skipped dates within the 7days. If there is, then it would be different than what you did manually by dividing 7.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[AmritaOS]

Hi Jihwan, yes there would be skipped dates. can i amend the calculation to include dates where there is a count >0 ?

thanks

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Do you mean you want to include the skipped dates + zero count dates in the 7days count?

If so, I think you need to have dim-date-table.

By using this, you cal have 7 counts for 7 days.