Forum Discussion
Month over month - waterfall chart
Hi there,
Every month I need to calculate monthly KPIs and present them vs the past 12 months.
My data is in the attached format, each row registers details for each order , with datecreated dates going back to Jan 2017.
I'd like to create a waterfall chart to show the total monthly sales evolution but I need help how to calculate the month over month variation ? Is there a way to do it similar to using the SAMEPERIODLASTYEAR for YoY var?
I
Hi,
Extract the Date from the datecreated field by using =INT(Data[datecreated]) calculated column formula (name this column as Date). Then create a new Table Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date])). Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table. In the Calendar Table, enter these calculated column formulas to extract Year and Month: Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm"). Drag Year and Month from the Calendar Table to your visual. Now write this measure:
Sales = SUM(Data[Amount USD])
Hope this helps.
6 Replies
- Ashish_Mathur
Super User
Hi,
Extract the Date from the datecreated field by using =INT(Data[datecreated]) calculated column formula (name this column as Date). Then create a new Table Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date])). Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table. In the Calendar Table, enter these calculated column formulas to extract Year and Month: Year = YEAR(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm"). Drag Year and Month from the Calendar Table to your visual. Now write this measure:
Sales = SUM(Data[Amount USD])
Hope this helps.
- Ashish_Mathur
Hi,
Sales in PM = CALCULATE([Sales],PREVIOUSMONTH(Calendar[Date]))
Growth in sales over PM (%) = IFERROR([Sales]/[Sales in PM]-1,BLANK())
Hope this helps.
v-jiascu-msftMicrosoft Employee