cancel
Showing results for
Did you mean:

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

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.

.

1 ACCEPTED SOLUTION
Super User

Hmm, that last sentence makes me think that this is for a grade...

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

Hmm, that last sentence makes me think that this is for a grade...

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Thanks,

RS

Super User

Awesome! Happy to help!

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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