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 am stucked with the following question.
Product Code | Sales | Date |
A | 10 | 01/02/2022 |
B | 10 | 01/02/2022 |
C | 10 | 01/02/2022 |
D | 10 | 01/02/2022 |
A | 10 | 01/03/2022 |
A | 10 | 01/03/2022 |
B | 10 | 01/03/2022 |
C | 10 | 01/03/2022 |
A | 10 | 01/04/2022 |
B | 10 | 01/04/2022 |
I would look for a equation that can calculate the difference between two period of time. In this data set every month I will update the sales record and I need a measure that can gives me somthing like below:
Assume today is Apr:
Product Code | Total Sales | Difference vs Last month |
A | 40 | 10 |
B | 30 | 10 |
C | 20 | 0 |
D | 10 | 0 |
What I need is the measure that can complete "Difference vs Last month" at the same time it does not require filter on visualization.
I can do this by using two spreadsheet inside powerBI, however, I hope this can be on 1 spreadsheet so everytime I dont have to refresh two file.
Is there any way to do this?
Thank you!
Solved! Go to Solution.
Hi,
I am not sure what's wrong with my equation as it keep return error message.
Hi @Anonymous
What is the difference?
Hey there!
Try this:
MoM Revenue Growth =
VAR _Actual =
Total
VAR _PrevMonth =
CALCULATE(
Total,
PREVIOUSMONTH( YourDateColumn )
)
RETURN
_Actual - _ PrevMonth
Let me know if that helps!
Hi,
I am not sure what's wrong with my equation as it keep return error message.
Can you please provide the error message you are getting? Thanks
The error message is like below:
The syntax for 'PrevMonth' is incorrect. (DAX(VAR _Actual = sum(''Table'[Sales])VAR _PrevMonth = CALCULATE( CALCULATE(sum('Table'[Sales])), PREVIOUSMONTH ('Table'[Date]) )RETURN _Actual - _ PrevMonth)).
@Anonymous
You have a space before PrevMonth. Delete it
Hi, the equation is working now but it only gives the sum of latest month data, I believe shuld be the PREVIOUSMONTH setting not working probaly, any advice on this?
Or can this criteria set as calculate the sum of the value except a sepecific date? Will that be easier?
Thanks!
Hi, I have fixed the issue and the equation is now working.
Thank you very much!
You can use either time intelligence build in function or just create common dimension column (i.e. month for you) and create a measure calculating sale difference based on current vs previous period.
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 |
---|---|
40 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
50 | |
26 | |
21 | |
17 | |
16 |