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.
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
2018 | 2018Q01 | 2018M01 | 2018W01 | 2302225 |
2018 | 2018Q01 | 2018M01 | 2018W02 | 2229061 |
Solved! Go to 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 :)))
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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |