Need to run a formula for output of Average Total Daily Revenue confined to a given month. A general example looks like this: Today is November 12, 2019, from November 1, 2019 to November 12, 2019 Busness A collected $98,000.00 in Revenue - Revenue (98,000) / Day of Month (12) = $8,166.67.
Problem: Initially I had the "Date" field on the X-Axis set to Distinct Count, with the following Aggregate formula:
- sum("Add Revenue"."Amount")/Distinctcount("Add Revenue"."Date")
The issue here is that if Business A does not collect Revenue on for example November 4, 2019 and November 7, 2019, the Distinct Count will calculate Day of Month as "10" and not "12".
Question: Does a Aggregate Formula exist to simply Sum(Revenue) then divide by Day of Month?