Forum Discussion

Balhemkan's avatar
Balhemkan
Helper III
3 years ago

Month average

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

Need Help

1 Reply

  • MohammadLoran25's avatar
    MohammadLoran25
    Solution Sage
    3 years ago

    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