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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leroy773
Helper II
Helper II

Calculate change of consumption qtr over qtr, but graph by non-date field

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.

 

CustomerSerial #Install DateDec To JanJan 2022Jan To FebFeb 2022Feb To MarMar 2022Mar To AprilApr-22April To MayMay-22May to JuneJun-22Sum Q2 Change
A112/1/20210 0 0 0 0 333
B210/26/2021-13-21560639-63-3
C312/17/2020314-59-36-4257071
C49/22/2021014-59-27-34-31781
D59/29/202144-4 0 1616-214-688

 

 

 

leroy773_0-1656872232669.png

 

CustomerSerial # DateUsage
A1 Jan 2021 
A1 Feb 2021 
A1 Mar 2021 
A1 Apr 2021 
A2 Jan 2021 
A2 Feb 2021 
A2 Mar 2021 
A2 Apr 2021 
C3 Jan 2021 
C3 Feb 20211
C3 Mar 20214
C3 Apr 20212
4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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.

leroy773
Helper II
Helper II

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. 

 

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)
        )
    RETURN
        SUM('Utliztion'[Consume]) - __PREV_QUARTER)

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.