cancel
Showing results for
Did you mean:
dimitrischo 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 v-qiuyu-msft
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 v-qiuyu-msft
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.
dimitrischo 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.

Highlighted
johanmeulenkamp 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.
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?

Cristina101083 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?

Philsap 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 Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 487 members 4,196 guests
Recent signins:
• Mann • tarunsingla • BrettBailey • danieljkim17 • edhans • APgoco • m535873 • AMaxamP3 • RoopaV • apache78 • CSG_ • gauthamboppana • Ankitkmr122 • swkalkar 