Reply
Member
Posts: 45
Registered: ‎02-26-2018

MoM% Measure Showing 0.00%

Hi,

I have below data 

 

DTMetricNameIndiaUSAService
9/30/2018Android 5450386353293885ALL
10/31/2018Android 5642195354058241ALL
9/30/2018Android1337093510232067Commercial
10/31/2018Android1400448110232067Commercial
10/31/2018Android40583793806905Commercial Education
9/30/2018Android38956183802905Commercial Education
9/30/2018Android4113292840029269Consumer
10/31/2018Android4241747240019269Consumer
9/30/2018iOS3620930435938371ALL
10/31/2018iOS3771791537131878ALL
9/30/2018iOS1726750610239136Commercial
10/31/2018iOS1809458610539136Commercial
10/31/2018iOS80106097617722Commercial Education
9/30/2018iOS77469637617222Commercial Education
9/30/2018iOS1894179818972020Consumer
10/31/2018iOS1962332918975020Consumer
9/30/2018Mac44074183243432ALL
10/31/2018Mac45589933397042ALL
9/30/2018Mac36452681528697Commercial
10/31/2018Mac37375121578697Commercial
10/31/2018Mac1194754724758Commercial Education
9/30/2018Mac1169288724258Commercial Education
9/30/2018Mac7621501092587Consumer
10/31/2018Mac8214811093587Consumer
10/31/2018Windows8218926363045256ALL
9/30/2018Windows7998523960670733ALL
9/30/2018Windows7464104345232740Commercial
10/31/2018Windows7670017845231740Commercial
10/31/2018Windows83243505476444Commercial Education
9/30/2018Windows82484025476424Commercial Education
9/30/2018Windows534419612337272Consumer
10/31/2018Windows548908512337072Consumer

 

Using below measure to to calculate MoM% change but I m getting 0.00% .let me know ho wto fix it

 

India MoM% = VAR PRE=CALCULATE( SUM('Sheet2'[India]),DATEADD('Calender'[Date], -1, MONTH)) VAR CUR = CALCULATE(SUM('Sheet2'[India]))
RETURN
IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)

image.png

 

 

Super User
Posts: 9,867
Registered: ‎07-11-2015

Re: MoM% Measure Showing 0.00%

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Member
Posts: 45
Registered: ‎02-26-2018

Re: MoM% Measure Showing 0.00%

@Greg_DecklerCan you share MoM% change formula in DAX without using time intellugence function?

Super User
Posts: 9,867
Registered: ‎07-11-2015

Re: MoM% Measure Showing 0.00%

Should be something along the lines of:

 

TITHW_%ChangeYoY = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay','Years'[Year]=__MaxYear,All('Months'[Month]))
VAR __currentMonth = SUMX(FILTER(__TmpTable,[MonthSort] = __MaxMonth),[Value])
VAR __previousMonth = SUMX(FILTER(__TmpTable,[[MonthSort] = __MaxMonth - 1),[Value])
RETURN DIVIDE(__currentMonth - __previousMonth,__previousMonth,0)

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 1,149
Registered: ‎07-30-2018

Re: MoM% Measure Showing 0.00%

hi, @atul9063

There is nothing wrong with your formula. since you need to calculate MoM%, you need to drag the field 'Calender'[Date]

from Calender table into visual, this will calculate with Time Intelligence.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 45
Registered: ‎02-26-2018

Re: MoM% Measure Showing 0.00%

ame tI have done thing but its not working.Anyway now im calculating in SQL

Highlighted
Community Support Team
Posts: 1,149
Registered: ‎07-30-2018

Re: MoM% Measure Showing 0.00%

hi, @atul9063

You could just drag the field 'Calender'[Date] into Columns value of Matrix like below:

10.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.