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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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. 🙂


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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.