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 recently took spreadsheet and converted it to a data table for powerBI, it required unpivoting of columns. This has worked, but I am curious if there is a way to create a measure or calculation that shows change over qtr over qtr by quantity or month over month by quantity and have it graphed based on customer name. Unfortunately, powerBI does not copy color in the table to make it easily displayed. The columns with To in the headers are columns I add to excel and calculate the difference from month to month or qtr to qtr. I then graph in excel as shown in the image below. Looking for a solution in powerBI. I currently import the spreadsheet into powerbi without calculations and transform the table to a date table with by unpivoting columns. Image below of how I transform the excel to data table for power bi without the individual calculations. @speedramps not sure if I explained it as well as last request.
Customer | Serial # | Install Date | Dec To Jan | Jan 2022 | Jan To Feb | Feb 2022 | Feb To Mar | Mar 2022 | Mar To April | Apr-22 | April To May | May-22 | May to June | Jun-22 | Sum Q2 Change |
A | 1 | 12/1/2021 | 0 | 0 | 0 | 0 | 0 | 3 | 3 | 3 | |||||
B | 2 | 10/26/2021 | -1 | 3 | -2 | 1 | 5 | 6 | 0 | 6 | 3 | 9 | -6 | 3 | -3 |
C | 3 | 12/17/2020 | 3 | 14 | -5 | 9 | -3 | 6 | -4 | 2 | 5 | 7 | 0 | 7 | 1 |
C | 4 | 9/22/2021 | 0 | 14 | -5 | 9 | -2 | 7 | -3 | 4 | -3 | 1 | 7 | 8 | 1 |
D | 5 | 9/29/2021 | 4 | 4 | -4 | 0 | 16 | 16 | -2 | 14 | -6 | 8 | 8 |
Customer | Serial # | Date | Usage | |
A | 1 | Jan 2021 | ||
A | 1 | Feb 2021 | ||
A | 1 | Mar 2021 | ||
A | 1 | Apr 2021 | ||
A | 2 | Jan 2021 | ||
A | 2 | Feb 2021 | ||
A | 2 | Mar 2021 | ||
A | 2 | Apr 2021 | ||
C | 3 | Jan 2021 | ||
C | 3 | Feb 2021 | 1 | |
C | 3 | Mar 2021 | 4 | |
C | 3 | Apr 2021 | 2 |
Hi @leroy773 ,
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj
Sorry for the delay was travelling. It is close, but all values are positive. When using a power pivot it would show positive and negative values thus indicating which customer utilization is decreasing over time or increasing.
I performed the following calculation, but it only displays using the date field and not the customer field.
Also since it is using the date key it only shows qtrs and not qtrs and years. It did not allow me to use qtr and year from my date dimension table.
Hi @leroy773 ,
According to your description, please try this code:
Value Change =
IF (
ISFILTERED ( 'DateDimension'[DateKey] ),
ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
VAR __PREV_QUARTER =
CALCULATE (
SUM ( 'Utilization'[Consume] ),
DATEADD ( 'DateDimension'[DateKey].[Date], -1, QUARTER ),
'DateDimension'[DateKey].[Year] = MAX ( 'DateDimension'[DateKey].[Year] ),
'Utliztion'[Customer] = MAX ( 'Utliztion'[Customer] )
)
RETURN
SUM ( 'Utliztion'[Consume] ) - __PREV_QUARTER
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |