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.
Hi,
For few days now I am trying to figure out how to do the following Q/Q measure in the matrix table with vendor and date column (the date column has been extended to the quarter view based on the hierachy)
where the formula of the excel formula looks like this
the powerBI desktop looks like this:
I tried numerous ways as per beginner to combat this easy-at-first task, searched this forum, watched youtube but nothing seems to work. What I need is Q/Q, Y/Y and 4Q/4Q but if I manage to get an idea how to do the company's revenue change for Q/Q only, I can figure out the rest.
Additionally (so I won't be duplicating the topics)
I have a market share to be calculated, so company's revenue change against the total market value, here is the excel formula I have done, thus the PowerBI formula I am looking to receive
I've tried Time Intelligence function but it adds up each of the quarter individually... not the last Q/Q, Y/Y, etc... Could anyone help my misery before the holiday?
May I ask if your problem has been solved? Is the above answer helpful to you?
The method provided by amitchandak is a good attempt.
If it helps, could you please mark the post which help as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
@TCop93 , with help from time intelligence you can get QOQ
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
YOY
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
Rolling 4Q = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-4,Quarter))
Rolling 4Q before 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-14),-4,Quarter))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Table Format is not possible
if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...
vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |