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.
Help! I am a newbie to Power BI and have run into 2 issues when attempting to calculate revenue growth against prior month. The first is that I only have value for each month, so Power BI is placing the prior month revenue for every day of the month as I have linked the revenue table to the date table. How do i suppress all of the days except the last day of the month. See the row with the 7.1% in the Revenue Growth PM column. That is the only correct record and there should only be one row for each month.
The second issue is the incorrect total for Revenue PM. How can I total all the unique Revenue PM to arrive at the correct total value?
TIA!!
Solved! Go to Solution.
Hi @bleumagnolia ,
I made a simple sample and create a measure as below:
_Revenue PM =
VAR _maxdate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Month] = MAX ( 'Calendar'[Month] )
&& 'Calendar'[Year] = MAX ( 'Calendar'[Year] )
)
)
VAR _sum =
CALCULATE ( SUM ( 'Table'[Revenue] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
IF ( MAX ( 'Calendar'[Date] ) = _maxdate, _sum )
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@VahidDM Not sure my reply to you went through as I don't see it on here, so apologies for the duplicate:
Here are the following DAX measures I created:
What is the relationship between those tables? have you changed that relation to see the result?
Appreciate your Kudos!!
Hi @VahidDM , for some reason, I kept getting an error when I tried to reply directly to your message, so had to reply outside of the direct reply.
Thanks!
Hi @bleumagnolia ,
I made a simple sample and create a measure as below:
_Revenue PM =
VAR _maxdate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Month] = MAX ( 'Calendar'[Month] )
&& 'Calendar'[Year] = MAX ( 'Calendar'[Year] )
)
)
VAR _sum =
CALCULATE ( SUM ( 'Table'[Revenue] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
IF ( MAX ( 'Calendar'[Date] ) = _maxdate, _sum )
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |