Reply
avatar user
Anonymous
Not applicable

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? 

 

IamountUSDPNG.PNG

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
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.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

One reference: Desktop/Sorting-by-Month-Number. Please check out.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
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.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Thanks but how do I calculate the variation in total sales from one month to the next?

Hi,

 

Sales in PM = CALCULATE([Sales],PREVIOUSMONTH(Calendar[Date]))

Growth in sales over PM (%) = IFERROR([Sales]/[Sales in PM]-1,BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Thank you, Ashish_Mathur I am almost there, I have created the waterfall visual with month and year out of the Date table I created, the issue now is the Month column is "text" and it does not arrange the months in cronological order in my visual - i tried to convert the column to date but it does not allow me. Any advice on this one ? 

 

Thank you,

M

Hi,

 

Please Google for "Sort months in PowerBI desktop".


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)