Forum Discussion
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?
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
5 Replies
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 YuHi 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.
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?
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?
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?
