Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
@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?
Hi there!
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |