Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |