Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WillemC
Resolver I
Resolver I

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 generates some nice code for me, but it's not working as I expected.

The code that's generated:

 

M.o.m. = 
IF(
	ISFILTERED('ds1'[levdate]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.");
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('ds1'[Weight]);
			DATEADD('ds1'[levdate].[Date]; -1; MONTH)
		)
	RETURN
		DIVIDE(SUM('ds1'[Weight]) - __PREV_MONTH; __PREV_MONTH)

I'm expecting that my sales for a month (i.e. april) is compared to the previous month march.

But when I create a simple matrix (using month as rows and m.o.m. as values), all values are 0%

 

When I change the -1 to -12 (a year), suddenly values appear in the table.

Why does it work if I look back one year, but not when I'm comparing one or some other amount of months?

 

I fiddled around with this, creating different tables, altering the code etc. But I can't get it to work to compare to the previous month.

 

Please enlighten me... ( I know I can create it with sql query on import, but I'm trying to learn the capabilities of Power BI)

 

 

1 ACCEPTED SOLUTION

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.

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @WillemC,

 

Can you please share some sample data to test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft,

 

some basic data I have (it's very long, multiple values per month and day for each month since januari 2016 till april 2017):

 

year,  month,  weight, levdat

2016,   1,   40180,   4-1-2016 0:00:00

2016,   1,   39420,   7-1-2016 0:00:00

2016,   1,   39740,   9-1-2016 0:00:00

2016,   1,   40320,   15-1-2016 0:00:00

2016,   1,   40100,   16-1-2016 0:00:00

2016,   1,   40040,   28-1-2016 0:00:00

2016,   2,   37980,   2-2-2016 0:00:00

2016,   2,   38100,   3-2-2016 0:00:00

2016,   2,   40240,   6-2-2016 0:00:00

2016,   2,   39940,   6-2-2016 0:00:00

2016,   2,   40160,   18-2-2016 0:00:00

2016,   2,   40020,   20-2-2016 0:00:00

....

2017,   2,   39880,   4-2-2017 0:00:00

2017,   2,   39680,   14-2-2017 0:00:00

2017,   2,   40520,   15-2-2017 0:00:00

2017,   2,   40240,   16-2-2017 0:00:00

2017,   2,   38980,   17-2-2017 0:00:00

2017,   2,   38000,   22-2-2017 0:00:00

2017,   2,   37180,   24-2-2017 0:00:00

2017,   2,   40520,   24-2-2017 0:00:00

2017,   2,   39700,   25-2-2017 0:00:00

2017,   2,   40060,   25-2-2017 0:00:00

.....

 

The code i'm using

 

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

 

and

 

 

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

 

this results in a matrix that looks like this:

 

month-over-month.JPG

I've noticed that if the -1 or -12 counts the number of rows back from the max month (12).

So If I fill in -8, then only the 62.25% is shown and the rest is 0%.

If I fill in -9 then the values for month 4 and 5 are shown (just a small preview of when I cange the code vor Weight 1 month to -9)

month-over-month-8.JPG

 

What I want to accomplish is that month 5 shows the amount vs month 4

now it compares to the same month previous year when I insert -12. So I was assuming that -1 compares to the previous month.

 

 

 

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.

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Follow-up to this.  When using the quick measure it only asks for one date which I would assume would come from the calendar (date) table.  The code displayed had to be editted to switch the data in the ISFILTERED to the date from the main table and to not use the Date table which I find confusing.  I would think the quick measure would just work and not require code editting.   Can some one explain this?

 

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

Thank you @v-shex-msft

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]

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.