Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mohan_g_das1986
Helper III
Helper III

Calculate YoY, QoQ, MoM & Wow units and how to show in visulizations

I have a data in the below format but i dont have the dates in the data due to large data set, can i be able to calculate YoY QoQ and others,... Also help me what is the best visulization i can share this to others

 

20182018Q012018M012018W012302225
20182018Q012018M012018W022229061
1 ACCEPTED SOLUTION

Hello Mohan 🙂

This is kind of a 1 fits all calculation for growth, just change the last parameter Quarter for Month , Year, Day , Week, what ever you need 🙂
and obviously , replace MEASURE with the calculation you need, 

 

Measure QoQ% = 
	VAR __PREV_YEAR = CALCULATE([Measure], DATEADD('Calendar'[Date].[Date], -1, QUARTER))
	RETURN
		DIVIDE([Measure] - __PREV_YEAR, __PREV_YEAR)

hope this helps
give a thumbs up if it does :)))

View solution in original post

4 REPLIES 4
Nickgastaldi
Resolver I
Resolver I

Hi Mohan,


I ain't no expert on PBI, but let me try to help you out 🙂

 

If the dataset is represented the way you presented, my approch to this would start in preparation, instead of calculation, i will take this a long run, so you can use Date-Time Functions instead of hard code calculation.

 

Go to the PowerQuery editor, you can use the UI to do this, 
Add a new Table (Calendar)

 

Calendar = CALENDAR(Datevalue("2018-01-01"),DATEVALUE("2019-12-31"))

add a columns

WeekKey = CONCATENATE(YEAR(date)CONCATENATE("W",WEEKNUM(date))



 

Now get our super fresh and new Calendar table and relation it with your fact table in the week column, try one of powerbi ready-made measures for YoY or MoM , and see if that solves your problem 
(and just copy paste their code for QoQ,WoW, or whatever works)

This is a Noobie solutions, i bet the pros will have some 1 line of code , 30s solution....but thats what i got 🙂

 

let me know if this helps in any way 🙂

 

 

hi created the the way you guided - any idea how to calcualte the Growth % 

Hello Mohan 🙂

This is kind of a 1 fits all calculation for growth, just change the last parameter Quarter for Month , Year, Day , Week, what ever you need 🙂
and obviously , replace MEASURE with the calculation you need, 

 

Measure QoQ% = 
	VAR __PREV_YEAR = CALCULATE([Measure], DATEADD('Calendar'[Date].[Date], -1, QUARTER))
	RETURN
		DIVIDE([Measure] - __PREV_YEAR, __PREV_YEAR)

hope this helps
give a thumbs up if it does :)))

Thanks for the reply, yeah will wait for experts option if not will use the same

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.