Forum Discussion
Help on DAX
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.
.
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:
- A measure called Revenue that shows the total amount of RevenueAmt.
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
- A measure called Revenue (PM) that shows the Revenue in the month prior to the month selected.
You would do something along the lines of Revenue (PM) = CALCULATE([Revenue],PREVIOUSMONTH('Calendar'[Date])
- A measure called Revenue MoM % that shows the month over month percent growth of Revenue.
That would be ([Revenue] - [Revenue (PM)) / [Revenue PM]
- A measure called FY17 Revenue that shows the Revenue for only the FY17 Fiscal Year.
You could do something like FY17 = CALCULATE([Revenue],FILTER('Calendar',YEAR([Date]) = 2017)
- 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.
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-TITHW/m-p/434008- A measure called Customer Count that shows the distinct number of customers with >0 Revenue in the given filtering context.
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. 🙂
3 Replies
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:
- A measure called Revenue that shows the total amount of RevenueAmt.
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
- A measure called Revenue (PM) that shows the Revenue in the month prior to the month selected.
You would do something along the lines of Revenue (PM) = CALCULATE([Revenue],PREVIOUSMONTH('Calendar'[Date])
- A measure called Revenue MoM % that shows the month over month percent growth of Revenue.
That would be ([Revenue] - [Revenue (PM)) / [Revenue PM]
- A measure called FY17 Revenue that shows the Revenue for only the FY17 Fiscal Year.
You could do something like FY17 = CALCULATE([Revenue],FILTER('Calendar',YEAR([Date]) = 2017)
- 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.
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-TITHW/m-p/434008- A measure called Customer Count that shows the distinct number of customers with >0 Revenue in the given filtering context.
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. 🙂
Awesome! Happy to help!