Calculate average number of transactions per month

[aquidato]

I currently have a table of request transactions with request id and created date.

I would like to get the average number of transactions per month based on this table.

Thanks in advance!

ID	Title	Created Date	Category
1	abc	01/01/2023	1
2	abcd	01/03/2023	2
3	abcde	02/03/2023	2
4	abcdef	02/06/2023	1

Cheers!

[amitchandak · Accepted solution]

@aquidato , Create a date table and join with your table and have month year there

Then create a measure like

Averagex(Values('DAte'[Month Year]), calculate(count(Table[ID]) ) )

Date table

date = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[aquidato]

thanks a lot @amitchandak , this worked out perfectly!