Forum Discussion

WillemC's avatar
WillemC
Icon for Resolver I rankResolver I
9 years ago
Solved

Month-over-month not working as expected

Hi all,   I'm new to Power BI and testing and trying out some different things to get the hang of the programm. At the moment I'm testing the Quick Measure Month-over-month change.   It generate...
Show and Tell
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi WillemC,

     

    I'd like to suggest you add a calendar table and use calendar date to calculate data.

     

    Steps:

    1. Use date from original table to create a calendar table, then add the relationship.

    Table = CALENDAR(FIRSTDATE(ALL('sample'[levdat])),LASTDATE(ALL('sample'[levdat])))

     

    2. Modify the formula and use the calendar date to calculate the data.

    weight 1 month = 
IF(
	ISFILTERED('sample'[levdat]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('sample'[weight]),
			DATEADD('Table'[Date].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH)
)

Weight MoM% 12 = 
IF(
	ISFILTERED('sample'[levdat]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('sample'[weight]),
			DATEADD('Table'[Date].[Date], -12, MONTH)
		)
	RETURN
		DIVIDE(SUM('sample'[weight]) - __PREV_MONTH, __PREV_MONTH)
)

     

     

    3. Create matrix visual with above data.

     

    Regards,

    Xiaoxin Sheng

WillemC's avatar
WillemC
Icon for Resolver I rankResolver I
9 years ago

Thank you Anonymous

This solution indeed works.

 

I don't understand why this does work, and my first one does not.

Both dates are formatted the same way.

I even find this option less logical, since I don't understand how this part works now:

 

CALCULATE(
			SUM('sample'[weight]),
			DATEADD('Table'[Date].[Date], -1, MONTH)
		)

How does Power BI knows what date to take here (for each period)?

It would be more logical to use the original 'sample'[Date].[Date]

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi WillemC,

 

I think these "date calculate" function are based on date column(date calcualte functions means the function which will calculate through the all records , for e.g "DateAdd", "PreviousMonth"...). If your amount value are in the same table, these function seems not able to calculate through all records (the calculation are been limited).

 

Regards,

Xiaoxin Sheng