Find Average Number of Transactions per Month

[Coriel-11]

I see this has come up elsewhere but the posted solutions aren't working for me.

In simplest terms I am trying to create a measure that find the average number of transactions that happen per month based on transaction date data as follows:

(Fact Table )Submitted date:

09/12/2020 12:19:12
17/12/2020 14:52:33
05/01/2021 13:31:26
26/01/2021 09:25:41
29/01/2021 16:12:00
05/02/2021 13:27:20
09/02/2021 12:06:16
15/02/2021 14:23:50
18/02/2021 09:59:01
19/02/2021 15:50:51
12/03/2021 09:07:46
09/04/2021 07:49:14

13/04/2021 09:59:01

19/04/2021 12:19:12
27/04/2021 14:52:33

So there's 15 transactions over 5 months so the average per month is 3

I have this table and have linked it to a (marked) date table

I've tried various variations on:

Ave per Month =

AVERAGEX(

    KEEPFILTERS(VALUES('Date Table'[Month])),

    CALCULATE(COUNTA'(Fact Table'[Submitted date]))

)

And I've tried putting the [Submitted date] field in both places. I've checked the hierarchy is in place, checked it's marked as date and that the relationship is linking in the right place. I don't know DAX very well at all, but I've found various entries online and tried them without success.

The annoying thing is that I did this before by just creating calculated columns in the Fact Table, but that doesn't seem to be the way you're meant to do it and I had to do various fiddles with the data which started giving inconsistent values, so I thought I'd try doing it the "right way", but total failure. I'm just geting the sum, or "1".

Thanks in advance,

Matt

[Coriel-11]

Thanks for this @Fowmy That's really helpful. I appreciate you taking the time to help.
One thing I don't understand though is that it doesn't use the date table and I thought the reason for having date/calendar tables was to be able to do time intelligence stuff like averages(?) (for example to be able to filter by year), whereas your solution seems closer to adding  a column (which I thought was generally to be avoided). Is this the case of their not being another way to do it? I'm just struggling to get my head round issue like combining efficiency and best practice with time intelligence functionality.
Many thanks again.
Matt

[Fowmy]

@Coriel-11 

I forgot you had a date table, please check the attached file. your formula would be:

Ave per Month = 
AVERAGEX(
   VALUES(Dates[Year Month]),
   CALCULATE(COUNTROWS(VALUES('Fact Table'[Transactions]))) 
)

[v-jingzhang]

Hi @Coriel-11 

Here is an overview of time intelligence functions in DAX: Time intelligence functions (DAX) - DAX | Microsoft Docs

There are several ways to calculate the average number of transactions per month. @Fowmy provides a way that calculates the average value without a date table. And it will not count the months which don't have transaction data.

If you want to use a Date table in this calculation, you need to pay attention to the date range in the Date table. If the Date table has more months than the Fact table, VALUES('Date Table'[Month]) will have more months than the actual number of months in Fact table. If transactions don't happen in every month, the month number will also be greater because a Date table always has continuous dates in it. 

Compared to whether using a Date table or not, it is more important to analyse the data you have and the expected result you want. Then we go on to decide which methods to calculate the result. Usually there are many ways to achieve the same result. Either one has its advantages. 

You may find below article helpful.

Do You Need a Date Dimension? - RADACAD

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Fowmy]

@Coriel-11 

You can create a measure as follows:

Measure = 
var __rows = COUNTROWS('Fact Table')
var __months = 
    COUNTROWS(
        SUMMARIZE(
            ADDCOLUMNS( VALUES('Fact Table'[Submitted Date]),"month", FORMAT('Fact Table'[Submitted Date],"mmm yyyy" )),
            [month]
        )
    )    
return
DIVIDE(__rows,__months)