cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dimitrischo Frequent Visitor
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...

 

WoWMoM
-0,5-0,25
-1-0,5
10,5

 

Untitled.png

 

Is there anyone who can help me?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator 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
Moderator 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
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.

johanmeulenkamp Frequent Visitor
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 ) )
    ))
    Knipsel.JPG

 

Could you please explain me what went wrong? 

Cristina101083 Frequent Visitor
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
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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 257 members 2,913 guests
Please welcome our newest community members: