topic Re: Help on DAX in DAX Commands and Tips

Help on DAX

Anonymous — Fri, 28 Feb 2020 07:48:26 GMT

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.

Re: Help on DAX

Greg_Deckler — Fri, 28 Feb 2020 08:08:19 GMT

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

Re: Help on DAX

Anonymous — Fri, 06 Mar 2020 04:32:26 GMT

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

Appreciated your response.

Thanks,

Re: Help on DAX

Greg_Deckler — Fri, 06 Mar 2020 04:43:33 GMT

Awesome! Happy to help!