Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

CALCULATE MOM COMPARISON

hello community, I have a table with fields for product, country month and sales...... how can I calculate month over month differences if dates and sales are each in the same field??
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Anonymous,

    Do you mean that you use a single column to hold information of dates and sales in your table? If that is the case, you would need to firstly split the column to two columns(Date column and Sales column), then you can directly create quick measure in Power BI Desktop to calculate MOM change.


    And you can make changes to the generated DAX formula to get MOM difference.

    MoM difference = 
IF(
	ISFILTERED('Sheet5'[DateKey]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Sheet5'[SalesAmount]),
			DATEADD('Sheet5'[DateKey].[Date], -1, MONTH)
		)
	RETURN
		SUM('Sheet5'[SalesAmount]) - __PREV_MONTH
)


    If the above steps don't help, please share sample data of your table.


    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      hi Anonymous ..

       

      thanks for your reply

       

      yes, the MOM comparisson worked perfectly, yet I want to do comparisson vs another month also and I have tried, several, SEVERAL things, but still not getting the correct visualization

       

      im attaching and excel file with the data set in one sheet, and another sheet with the waterfall graph that I want to obtain in PowerBI.

       

      I want to have, everymonth,  a graph of Current Month (for this time july) vs previous month.... and another of current month vs Dec 2016... but im still not getting it right.

       

      hope anyone here can help me, will be very appreciated.

       

      DEMO DATA FILE https://drive.google.com/file/d/0B4nVbsKyp4MfMFd6NWFmQkpOeEE/view?usp=drivesdk

       

      ps for waterfall im not using the "breakdown" functionality and using time-based calculated measures instead because i want to be able to drill further down in the differences (ie, the MOM difference is 118, i want to drill down in the 118 and see which countries/products makes that 118 difference.... as in the pbix file attached).. i want to have this both for MOM and for vs 2016 graphs.