Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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.

 

.Customer.PNG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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-TIT...

  • 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. 🙂



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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-TIT...

  • 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. 🙂



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you Greg. I just had to make minor tweaks. It worked fine.

Appreciated your response.

 

Thanks,

RS

 

Awesome! Happy to help!



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.