Month average

[Balhemkan]

Hi Team, 

I have one transaction table on that i have values from Jan -2022 to Jan - 2023

But Oct data is missing in that. Month itself not available in transaction data.

I have Date table and Transaction table

In date table i have Date Column

In Transaction table i have two Columns Date column and Sales Amount

I give join from Date table Date column to Transaction Table Date Column

My requirement is if i select only one month in Date table Date column I need to get sum of Sales amount for that particular month. If i select more than one month in Date column i need to get Average for selected months.

I written below formula

Countmonths =  MONTH(COUNT(Date_table[date]))

Sales =

IF([countmonths]<=1,SUM(Facttable[Sales]),IF([countmonths]>1,SUM(Facttable'[Sales])/[countmonths]))

This is working if i select Jan-22 to Sep -22 but if i select Oct to dec its not working properly because oct-22 is not available

Thanks, 

Balhemkan

[MohammadLoran25]

Hi @Balhemkan 

To get your problem solved:

At first make a measure:

SalesAmount=SUM(Facttable'[Sales])

Then make another measure for your Average as below which refers to previous measure:

AVERAGESalesAmount=

AVERAGEX(VALUES(Date_Table[YearMonth]),[SalesAmount])

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.

Regards,

Loran