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.
Hi, I wanna calculate the average of last year sum but it seems not working with the formula below. Please help.
Sample here.
AUSP Annual =
AVERAGEX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
CALCULATE([AUSP]))*12
AUSP Annual Last Year =
AVERAGEX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
CALCULATE([AUSP Last Year]))*12
Solved! Go to Solution.
Hi, @PBI_newuser
You can see that the table don't have over 2019-3 dates,so the value is blank.
You can try this:
AUSP Annual Last Year =
CALCULATE([AUSP Annual],SAMEPERIODLASTYEAR('Calendar'[Date]))
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @PBI_newuser
Try:
Volume Impact =
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
)
)
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @PBI_newuser
According to your description, [AUSP Last Year] should be a measure, can you share the formula because the formula you provided has no no syntax errors? If you can share some sample fake data we can help you soon.
Best Regards
Janey Guo
Hi @v-janeyg-msft , here is the sample data. Are you able to open the pbix file?
https://drive.google.com/file/d/1SppgTWyr7Zy4orH3YkuGGE72UjifNuC9/view
Hi, @PBI_newuser
I can see the file, Can you show me your desired result like? I need to rewrite the code according to your logic and result.
Hi @v-janeyg-msft , i want the same result for "AUSP Annual" in FY2019 show up under "AUSP Annual Last Year" field in FY20.
Hi, @PBI_newuser
You can see that the table don't have over 2019-3 dates,so the value is blank.
You can try this:
AUSP Annual Last Year =
CALCULATE([AUSP Annual],SAMEPERIODLASTYEAR('Calendar'[Date]))
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft , it works! Thank you so much! 😊
I have one more question. How to set those blank cell to 0?
For the below measure, I couldn't calculate my "Volume Impact" correctly.
If the [Volume Current Year] = Blank, set it as 0 so that it can minus [Volume Last Year].
Here is the sample.
Volume Impact =
SUMX(
SUMMARIZE('Append Table','Calendar'[YearMonth]),
IF([Volume Last Year]<=0,([Revenue Current Year]-[Revenue Last Year]),
([Volume Current Year]-[Volume Last Year])*[AUSP Last Year]))
Try :
new measure=if([column current year]=blank(),0,[column current year])
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There is a problem with your measure, I said before.
What do you want the following three values? I can only restore the results you need as much as possible.
@v-janeyg-msft , I am so sorry, i misunderstood your question.
I want to calculate the volume impact in each month then sum it up as total for the selected FY.
For Product B, C & D, the sum of volume impact for the year should be 0.
@v-janeyg-msft , below values should be displayed in the table. Thanks.
Product | Volume Impact |
B | -3262 |
C | -331 |
D | -3192 |
Hi, @PBI_newuser
Try:
Volume Impact =
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
),
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth] ),
IF (
[Volume Last Year] <= 0,
( [Revenue Current Year] - [Revenue Last Year] ),
( [Volume Current Year] - [Volume Last Year] ) * [AUSP Last Year]
)
)
)
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft , sorry to bother you, I have one last question.
I tried to modify your measure by multiplying (-1) as I wanna show negative values for Volume Impact if [Volume Current Year] = 0. I noticed some of the products have incorrect sum of [Volume Impact].
For example, for Product E, the [Volume Impact] for each month is correct. But when it sum up for FY2020, it became incorrect. It should be -28022 instead of -13369. I couldn't locate the error. Could you please help me? Thank you so much!
Here is the sample.
Hi, @PBI_newuser
This is not a simple problem. The root cause of the problem is the mismatch of date data. You have to modify more conditions to solve it. But I don't have time to check it now, I will reply to you when I am free, or you can choose to open a new case.
Best Regards
Janey Guo
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |