How to calculate an amount in a column of invoices according to a column of months?

[Shlomiaf7]

How can I calculate an amount in a certain column according to the month that is in the same row with it in the invoices table and it is linked to a table of dates, I want to calculate a total amount for each month separately throughout all the years (for example: January 2018-2023, February 2018-2023, etc.).
I need to average by month over the years.
Many thanks to the helpers.facr invoices tabledim date table

[v-heq-msft · Accepted solution]

Hi @Shlomiaf7 ,

Here are my test data snapshots. There are two tables.

Then follow steps:

1.To achieve calculate total according to selected year and month range, create measure like below:

Total = 
var _years=ALLSELECTED('date'[Year]) 
var _months=ALLSELECTED('date'[Month])
RETURN CALCULATE(
    SUM('invoices'[Amount]),
    FILTER(
        'invoices',
        YEAR('invoices'[year_month]) in _years && MONTH('invoices'[year_month]) in _months
    )
)

2. To achieve calculate average according to selected year and month range, create measure like below:

Average = 
var _years=ALLSELECTED('date'[Year]) 
var _months=ALLSELECTED('date'[Month])
RETURN CALCULATE(
    AVERAGE('invoices'[Amount]),
    FILTER(
        'invoices',
        YEAR('invoices'[year_month]) in _years && MONTH('invoices'[year_month]) in _months
    )
)

3.Final output

In order for you to solve the problem faster, you can refer to the following documentation.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best regards,

Albert HE

[lbendlin]

according to the month that is in the same row with it in the invoices table

not sure what you mean by that. What is the join column?

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...