Does anyone know how to calculate a measure with only the months that have data in it?

[GenericUser1]

I have data from March to August. Every other month is missing data. I want to look for the average number of tickets per month as a KPI but only with the months with data in it. 

[v-yuta-msft]

Hi GenericUser1,

As a general solution, you may create a measure using DAX like pattern below and check if it can meet your requirement:

Average Value Per Month =
CALCULATE (
    AVERAGE ( Table[Number of Tickets] ),
    ALLEXCEPT ( Table, Table[Month] )
)

Regards,

Jimmy Tao

[GenericUser1]

This dax for some reason is returning the total number of tickets I have. The average should be around 30 something per month but I don't know how to get there without including months with no data in it.  

AverageTicketsPerMonth = CALCULATE(
average(incidents[Number Of Tickets Column]),
allexcept('Calendar Date', 'Calendar Date'[MonthShort]

result:

341. 

[edhans]

Is your MonthShort column by chance being sorted by another column? If so, include that column in your ALLEXCEPT as well.

[GenericUser1]

This dax for some reason is returning the total number of tickets I have. The average should be around 30 something per month but I don't know how to get there without including months with no data in it.  

AverageTicketsPerMonth = CALCULATE(
average(incidents[Number Of Tickets Column]),
allexcept('Calendar Date', 'Calendar Date'[MonthShort]

result:

341. 

[edhans]

The AVERAGE() function ignores missing data. I just put test data in January, March, and July. Then I connected it to my Date table. Dropped teh month from the date table into a Table, then created this measure:

=AVERAGE(Tickets[Records])

The grid shrank to three rows - Jan/Mar/Jul, and hte average that was calculated is accurate.

I did not try this without a Date table. I alwys have a date table in my model.

[GenericUser1]

Good suggestion but I was hoping to use the average per month as a KPI so not in bar graph. I'm not looking for average in January, February etc... Just a general average per month if you get what I mean so using a card visual to check the overall average per month.