Avg number of items per month (only last completed month)

[jcastr02]

I am trying to do an avg number of records per month but I only want it to count through the last complete month.  In other words, if today is 11/8/2024, I want it to only    count records through 10/31/2024.  

My table has below two columns

RecordID           Created           

[saud968 · Accepted solution]

To calculate the average number of records per month up to the last completed month in Power BI, you can follow these steps:

Create a new column to extract the month and year from the Created column:
MonthYear = FORMAT('YourTable'[Created], "YYYY-MM")

Create a measure to count the records up to the last completed month:
TotalRecords =
CALCULATE(
COUNT('YourTable'[RecordID]),
'YourTable'[Created] < EOMONTH(TODAY(), -1) + 1
)

Create a measure to count the distinct months up to the last completed month:
DistinctMonths =
CALCULATE(
DISTINCTCOUNT('YourTable'[MonthYear]),
'YourTable'[Created] < EOMONTH(TODAY(), -1) + 1
)

Create a measure to calculate the average number of records per month:
AvgRecordsPerMonth =
DIVIDE([TotalRecords], [DistinctMonths])

Replace 'YourTable' with the actual name of your table. These steps will give you the average number of records per month up to October 31, 2024.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!