Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello,
I have 3 tables called Customer, Revenue and Calendar. I am looking for DAX on the following measures.
A measure called Revenue that shows the total amount of RevenueAmt.
A measure called Revenue (PM) that shows the Revenue in the month prior to the month selected.
A measure called Revenue MoM % that shows the month over month percent growth of Revenue.
A measure called FY17 Revenue that shows the Revenue for only the FY17 Fiscal Year.
A measure called Revenue YTD that shows the year to date Revenue based on the selected year/month. The fiscal calendar starts July 1 and ends June 30.
A measure called Customer Count that shows the distinct number of customers with >0 Revenue in the given filtering context.
Format the model to the best of your ability, as if it were to be showcased to an executive.
.
Solved! Go to Solution.
Hmm, that last sentence makes me think that this is for a grade...
Sample data always helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said:
So, you should be able to use something like Revenue = SUM([Revenue]) and put this in a visual along with Month and Year, perhaps in a Matrix
You would do something along the lines of Revenue (PM) = CALCULATE([Revenue],PREVIOUSMONTH('Calendar'[Date])
That would be ([Revenue] - [Revenue (PM)) / [Revenue PM]
You could do something like FY17 = CALCULATE([Revenue],FILTER('Calendar',YEAR([Date]) = 2017)
Similar you could use TOTALYTD here although the fiscal calendar might throw a wrench into it. If all else fails, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
So, you would do something like My Count = COUNTX(FILTER('Table',[Revenue] > 0),[Column])
Again, these are only guesses because no data. And don't blame me if you get an F. 🙂
Hmm, that last sentence makes me think that this is for a grade...
Sample data always helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said:
So, you should be able to use something like Revenue = SUM([Revenue]) and put this in a visual along with Month and Year, perhaps in a Matrix
You would do something along the lines of Revenue (PM) = CALCULATE([Revenue],PREVIOUSMONTH('Calendar'[Date])
That would be ([Revenue] - [Revenue (PM)) / [Revenue PM]
You could do something like FY17 = CALCULATE([Revenue],FILTER('Calendar',YEAR([Date]) = 2017)
Similar you could use TOTALYTD here although the fiscal calendar might throw a wrench into it. If all else fails, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
So, you would do something like My Count = COUNTX(FILTER('Table',[Revenue] > 0),[Column])
Again, these are only guesses because no data. And don't blame me if you get an F. 🙂
Thank you Greg. I just had to make minor tweaks. It worked fine.
Appreciated your response.
Thanks,
RS
Awesome! Happy to help!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.