A while ago an eCommerce store told me that their goal was to increase sales to Z. To translate that goal into a day to day KPI, the easiest explanation was this:

Today your Average Order Value is

X. To reach your goal ofZ, yournumber of Transactions per Dayhave to beY.

So, when Google Data Studio finally became available in Norway, it was time to create the **Average Transactions per Day** KPI in **Google Data Studio**.

## Calculate number of days in Data Studio using DATE_DIFF & TODATE

In this example I’m using data from Google Analytics, so if you are going to calculate this using a different data source, you may have to tweak the date part of my example.

To calculate number of days we have to use 2 functions, **DATE_DIFF** and **TODATE**. In Google Data Studio you find this example/help text about how to do this calculation:

1 2 3 4 5 6 7 8 9 |
DATE_DIFF(X, Y) Summary Returns the difference in days between X and Y (X - Y). X A datetime returned by the TODATE function. Y A datetime returned by the TODATE function. Example DATE_DIFF(TODATE(end_time, 'NANOS', '%Y-%m-%d'), TODATE(start_time, 'MICROS', '%Y%m%d')) |

The example above seems to confuse some people. They try to **create a new metric** using the calculation exactly as it is written, which isn’t possible, and an error is returned. The problem is that **end_time** and **start_time** in the calculation are just *dummy text* telling you to input your **end time** and **start time**.

If you are going to **calculate number of days** using **Google Analytics** as a** Data Source**, the example below will get you there.

1 |
DATE_DIFF(TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'), TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d')) |

If you wonder about the **DEFAULT_DASH** part, see TODATE input formats.

In my example I call this new metric for **Date Diff.**

Notice that I have chosen **Count** as **Aggregation**.

## Calculate Average Transactions per Day

To calculate **Average Transactions per Day**, **create a new metric** using the calculation below. I have called the metric **Transactions per Day****.**

1 |
Transactions / Date Diff |

*Job completed! *

Now make the Average Transactions per Day KPI fit into your dashboard design in Google Data Studio.

Thanks for the formula, works great for getting the date range as a metric.

I’m having trouble with using it in a calculated metric though. I checked it’s output and it’s right, but when I divide users by date diff I’m getting discrepancies from the right answer up to 40% (ex users/date diff=avg users per day: 43979/30=2233 when the right answer is 1466). Any thoughts where this is going wrong?

Thank you! this is very helful, any chance you can provide a custom metric formula to calculate “current days of the month) for example if today is June 13 it will return “13” and also days to the end of the month (if the month has 30 days and there are 17 days left will return “17”?

Thanks!

Fantastic! Thank you for the formula.

Is this function only intended for 2 static dates or can it be used between 1 static date and the current date?

If I understand your question correctly, it can be used between 1 static date and the current date.

Regards,

Eivind