cancel
Showing results for
Did you mean:
Frequent Visitor

## Week on Week , Month on Month Analysis

Hi,

I am a noob to power bi and i am trying to give reports that they are going to have week on week analysis and month on month.

This moment i Have all of my data in excel which are rows from multiple users

Example.

Date Name Sales

1/1/2016 Dimitris 10

1/1/2016 John 12

1/2/2016 Dimitris 14

1/2/2016 John 20

1/8/2016 Dimitris 4

1/8/2016 john 12

etc....

So now I need to group them by week and every week to be automated updated with the last week to be the max week on my data and the previous one. And next to that percentage % of change to have a green icon if it is positive and a red one if it is negative. more specific like this one in excel...

 WoW MoM -0,5 -0,25 -1 -0,5 1 0,5

Is there anyone who can help me?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Week on Week , Month on Month Analysis

Hi @dimitrischo,

To calculate the difference month on month and week on week, you can create a calendar table, and build the relationship based on date value between calendar table and fact table. Then create measures like below:

MOM =
VAR PreMonth = CALCULATE( MAX( 'Calendar'[Month] ) ) - 1
return
( IF(CALCULATE( MAX( 'Calendar'[Month] ) )=1,BLANK(),
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) )
))

WOW =
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1,BLANK(),
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) )
))

But currently we are not able to add indicators in a table, you need to create Power Pivot model and KPIs in a workbook firstly then import it to Power BI Desktop. See: Import and display KPIs.

Best Regards,
Qiuyun Yu

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

## Re: Week on Week , Month on Month Analysis

Hi @dimitrischo,

To calculate the difference month on month and week on week, you can create a calendar table, and build the relationship based on date value between calendar table and fact table. Then create measures like below:

MOM =
VAR PreMonth = CALCULATE( MAX( 'Calendar'[Month] ) ) - 1
return
( IF(CALCULATE( MAX( 'Calendar'[Month] ) )=1,BLANK(),
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) )
))

WOW =
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1,BLANK(),
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) )
))

But currently we are not able to add indicators in a table, you need to create Power Pivot model and KPIs in a workbook firstly then import it to Power BI Desktop. See: Import and display KPIs.

Best Regards,
Qiuyun Yu

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

## Re: Week on Week , Month on Month Analysis

Hi v-qiuyu-MSFT,

The info that you gave me are great! but i have only one issue. on my row data i have some categories. and when i apply a filter on report with this column doesn't calculate right. How can i Fix that?  I think this is happening because of the filter All.

Frequent Visitor

## Re: Week on Week , Month on Month Analysis

Hi there!

• I tried this solution as well, but it didn't work out (see attached example and your solution). I'm learning some DAX-coding now, so I'm not super experienced yet. WOW makes the wrong calculation.
• I would like to and have the Power BI-files availabe to test and check.
```Your Example
WOW =
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1;BLANK();
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ); FILTER( ALL('Fact'); WEEKNUM( 'Fact'[Date] ) = PreWeek ) ))/CALCULATE( SUM( 'Fact'[Sales] ); FILTER( ALL('Fact'); WEEKNUM( 'Fact'[Date] ) = PreWeek ) )
))

My Example
WOW =
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1;BLANK();
(SUM('Sessions'[Traffic])-CALCULATE( SUM( 'Sessions'[Traffic] ); FILTER( ALL('Sessions'); WEEKNUM( 'Sessions'[Date]) = PreWeek ) ))/CALCULATE( SUM( 'Sessions'[Traffic] ); FILTER( ALL('Sessions'); WEEKNUM( 'Sessions'[Date] ) = PreWeek ) )
))```

Could you please explain me what went wrong?

Highlighted
Frequent Visitor

## Re: Week on Week , Month on Month Analysis

I dont have this option of PreWeek.

I can see Prewek, but it tells me that it does not work.

see my formula:

WOW = VAR Prewek=calculate(max(DimDate[Week]))-1
return
(if(calculate(max(DimDate[Week]))=1,BLANK(),
[DI Rigs]-calculate([DI Rigs],filter(all('Drilling Info_Final_Data'),WEEKNUM('Drilling Info_Final_Data'[Friday_Date])=Prewek)))/CALCULATE([DI Rigs]),FILTER(all('Drilling Info_Final_Data'),
WEEKNUM('Drilling Info_Final_Data'[Friday_Date])=Prewek))

Can I get some help?

Regular Visitor

## Re: Week on Week , Month on Month Analysis

@v-qiuyu-msft Hey I am looking to do the same thing and I tried the solution and it's not working. The results are showing "infinity" could you take a look at my workbook below and if the same formula could be applied?

https://we.tl/nJdKC2ez8V

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 104 members 1,729 guests
Recent signins: