Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |