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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors