Calculating rate of an event/day when event doesn't happen every day

[pagrosse]

Hey guys,  If I use the following calculation:

Event rate = COUNT[Events]/DISTINCTCOUNT[Date]

I find that the numerator is lower by the number of days an event didn't happen.  For example, if I plot Event rate vs. time (by month) my rate will be artificially high if there are dates no event occurred because it will not count these dates.

Any suggestions?  I've also tried using DATEDIFF(MIN(Date), MAX(Date),DAYS) which gets me closer but will also be artificially high if an event doesn't happen on the first or last date.

Thanks!

[pagrosse · Accepted solution]

I figured out a solution that is working.  It required the use of a date table and creating a relationship in the model.  Then I could use as my denomentator Countrows('DateTable'[Day])

[jt1024]

Does that mean your calendar table only spans the same date range as your data table?

When I have a calendar table that spans a date range greater than the source data, and I use the measure:

Event rate = COUNT[Events]/Countrows('DateTable'[Day])

The rate value is affected by the size of the Calendar table.

I.e. If I set my calendar table from data start to 2099, the rate is super low.

If I set my calendar table (m query source) from data start date to 20211, the rate is higher. (Okay that may be a bit captain obvious, but I wanted to be clear.)

This is what I did to get mine to work, verified with an excel manual calculation:

Created Ticket Date Range =

DATEDIFF(

MIN('JIRA CSV Exports'[Created]),

MAX('JIRA CSV Exports'[Created]),

DAY)

Created Ticket Count =

MAX('JIRA CSV Exports'[Index])

there's an index starting at 1 in my main data table (JIRA CSV Exports)

Created Ticket Rate = [Created Ticket Count] / [Created Ticket Date Range]

Happiness! Thanks for your original post.

[pagrosse]

Thanks for the suggestion!  I tried that but it still does not account for days that no event occured.  I attached a test data set.  When I select a particular week, only 5 distinct day counts even though there was 7 days.  I want to get the 3.71events/day vs. the 5.20events/day (basically dividing the number of events by 7 instead of 5)

https://1drv.ms/u/s!AmSn6NUGP_obgx6AgwVe8naxVkDc?e=EQVesd 

[v-yingjl]

Hi @pagrosse ,

Based on your sample data and description, what cnfused me is that since there are 7 days in a week, just divide by 7 directly as you write in the formula. Why do we need to get 7 by calculation as the denominator? 

Maybe you can consider sharing more details about this issue for further discussion.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@pagrosse , Try a measure like

Event rate = divide(COUNT(Table[Events]),calculate(DISTINCTCOUNT(Table[Date]), filter(Table, not(isblank(Table[Events])))))