Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to reference differences between columns of visualization

I am needing help, please, and I'm very new to PBI so any help with explaining logic if solving this with DAX would be very much appreciated!

 

Below (Screen Shot #1) is a visualization showing customer invoices from month to month. I'm trying to figure out a way of referencing the previous month to see if there is either a + or - change of, say, 5% and, if so - the invoice amount is highlighted red. In this manner, a member of the sales team can quickly review the list of cusotmers for the current month and see, quickly, if there was a deviation from the previous month.

 

The two tables (I was told to create a date table to do the date comparisons) are in screenshots 2 & 3. The "AGR_Header_RecID is essentially the customer ID (as it's represented in an agreement table). The main key values in the table are:

 

  • Company_RecID = Customer
  • Monthly_Inv_Amt = The amount of the bill
  • PQ_Date = Date of the bill

Any help would be VERY much appreciated.

 

 

SCREEN SHOT #1

 

SCREEN SHOT #2

 

SCREEN SHOT #3

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, you can create a measure like this:

    Measure =
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Company_Name] = MAX ( 'Table'[Company_Name] )
                && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
                && MONTH ( 'Table'[Date] )
                    = MONTH ( MAX ( 'Table'[Date] ) ) - 1
        ),
        'Table'[Invoice]
    )
RETURN
    IF (
        _Pre <> BLANK ()
            && DIVIDE ( MAX ( 'Table'[Invoice] ) - _Pre, MAX ( 'Table'[Invoice] ) ) > 0.03,
        "RED"
    )

    In the visual formatting pane>Cell elements, turn on the Font color, apply the measure to the Invoice color.

    Get the result.

    I attach my sample below for reference.

     

    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.

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    " I'm trying to figure out a way of referencing the previous month"

     

    Power BI has no concept of "previous" or "next" column or row.  You need to explain to the engine how your data is sorted and what the intervals are.

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, you can create a measure like this:

    Measure =
VAR _Pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Company_Name] = MAX ( 'Table'[Company_Name] )
                && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
                && MONTH ( 'Table'[Date] )
                    = MONTH ( MAX ( 'Table'[Date] ) ) - 1
        ),
        'Table'[Invoice]
    )
RETURN
    IF (
        _Pre <> BLANK ()
            && DIVIDE ( MAX ( 'Table'[Invoice] ) - _Pre, MAX ( 'Table'[Invoice] ) ) > 0.03,
        "RED"
    )

    In the visual formatting pane>Cell elements, turn on the Font color, apply the measure to the Invoice color.

    Get the result.

    I attach my sample below for reference.

     

    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.