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.
Hello,
I am new to Power Bi. I am trying to calculate the percentage change in the number of customers quarter on quarter as well as year on year based filter applied on two columns- Energy and Supplier. I tried below code but I guess I am going wrong with the Date logic.
change perc = VAR ValueLastYear =
CALCULATE (
SUM ( ElectricityGasCustomerNum[CustomerNumber] ),
FILTER (
ElectricityGasCustomerNum,
ElectricityGasCustomerNum[Date]
= ( EARLIER ( ElectricityGasCustomerNum[Date] ) - 1 )
&& ElectricityGasCustomerNum[Energy] = EARLIER ( ElectricityGasCustomerNum[Energy] )
&& ElectricityGasCustomerNum[Supplier] = EARLIER ( ElectricityGasCustomerNum[Supplier] )
)
)
RETURN
IF (
ISBLANK ( ValueLastYear ),
0,
( ElectricityGasCustomerNum[CustomerNumber] - ValueLastYear )
/ ValueLastYear
)
Date | Energy | Supplier | CustomerNumber | %Change |
01/01/2009 | Electricity | A | 40000 | |
01/04/2009 | Electricity | A | 190000 | |
01/07/2009 | Electricity | A | 260000 | |
01/10/2009 | Electricity | A | 320000 | |
01/01/2010 | Electricity | A | 370000 | |
01/04/2010 | Electricity | A | 410000 | |
01/07/2010 | Electricity | A | 400000 | |
01/10/2010 | Electricity | A | 450000 | |
01/01/2009 | Electricity | B | 25000 | |
01/04/2009 | Electricity | B | 140000 | |
01/07/2009 | Electricity | B | 100000 | |
01/10/2009 | Electricity | B | 200000 | |
01/01/2010 | Electricity | B | 310000 | |
01/04/2010 | Electricity | B | 280000 | |
01/07/2010 | Electricity | B | 350000 | |
01/10/2010 | Electricity | B | 390000 | |
01/01/2009 | Gas | A | 30000 | |
01/04/2009 | Gas | A | 90000 | |
01/07/2009 | Gas | A | 60000 | |
01/10/2009 | Gas | A | 30000 | |
01/01/2010 | Gas | A | 290000 | |
01/04/2010 | Gas | A | 320000 | |
01/07/2010 | Gas | A | 390000 | |
01/10/2010 | Gas | A | 370000 |
Any help would be greatly appreciated.
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
Check this file: Download PBIX
The key point is to have a date calendar, so you can use the data intel functions.
Did I answer your question? Mark my post as a solution!
Ricardo
Hi @Anonymous ,
Check this file: Download PBIX
The key point is to have a date calendar, so you can use the data intel functions.
Did I answer your question? Mark my post as a solution!
Ricardo
Hi @camargos88 ,
Thank you for your solution. It worked. I am building a dashboard for this type of data. It would be great if you could suggest what all insights I can show may be as a summary using the charts of power bi.
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 |
---|---|
47 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |