cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
rekhasagar04
Frequent Visitor

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


@ 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!:
Mastering Power BI 2nd Edition

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Appreciated your response.

 

Thanks,

RS

 

Awesome! Happy to help!


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors