Month on Month Difference of Revenue Per Customer

[markefrody]

Hi, 

I'm new to Power BI and would like to request your kind assistance. 

Below is the sample raw data that I am using.  I am using DIRECT QUERY since I want live data. Data is from SQL.
Please note that clients can have multiple transactions within the given date:

Client Name	Date	Month	Month Name	Year	Revenue
Ferro Steel Inc.: Los Angeles	2017-06-31 00:00:00.000	6	June	2017	899.29
Harris Supply Solutions: Seattle	2017-06-29 00:00:00.000	6	June	2017	35
Harris Supply Solutions: Seattle	2017-06-29 07:49:00.000	6	June	2017	300
USS/POSCO: Pittsburg	2017-06-15 06:52:31.000	6	June	2017	108
Ferro Steel Inc.: Los Angeles	2017-07-07 00:00:00.000	7	July	2017	468
Harris Supply Solutions: Seattle	2017-07-27 00:00:00.000	7	July	2017	50
USS/POSCO: Pittsburg	2017-07-02 00:00:00.000	7	July	2017	790

I'm trying to get the difference and % difference of month per month revenue for each customer similar to this:

	January 2017			February 2017			March 2017
Client Name	Total Revenue	Diff last month	% Diff last month	Total Revenue	Diff last month	% Diff last month	Total Revenue	Diff last month	% Diff last month
Client 1	100	-20	10%	300	200	300%	50	-250	17%
Client 2	200	50	9%	70	-130	35%	600	530	857%
Client 3	300	60	25%	75	-225	25%	100	25	133%

Is the above possible doing in table or matrix form? If yes, how?

Appreciate all your kind advice and support.

Best regards,

Mark

[v-ljerr-msft]

Hi @markefrody,

Based on my test, you should be able to use the formula below to create a measure to calculate total revenue last month.

Revenue last month = 
VAR currentYear =
    MAX ( Table1[Year] )
VAR currentMonth =
    MAX ( Table1[Month] )
VAR currentClient =
    FIRSTNONBLANK ( Table1[Client Name], 1 )
RETURN
    CALCULATE (
            SUM ( Table1[Revenue] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Year] = currentYear
                    && Table1[Month]
                    = currentMonth - 1
                    && Table1[Client Name] = currentClient
            )
        )

Then use formula below to create measures to calculate the Diff last month and % Diff last month. 

Diff last month = SUM(Table1[Revenue])-[Revenue last month]

% Diff last month = DIVIDE(SUM(Table1[Revenue]),[Revenue last month])

Note: just replace Table1 with your real table name.

Regards

[markefrody]

Hi @v-ljerr-msft!

Thank you for providing me a formula to start with.  But I getting an error stating:

"A function 'FILTER' has been used in a True/False expression that is used a table filter expression. This is not allowed."

Here is th formula I am using based on your formula. Please let me know if I did anytrhing wrong. Thanks.

Last Month =

VAR currentYear =

MAX ('RFS Invoice Revenue by Item Client Month - Power BI'[Item Year])

VAR currentMonth =

MAX ('RFS Invoice Revenue by Item Client Month - Power BI'[Item Month])

VAR currentClient =

FIRSTNONBLANK ('RFS Invoice Revenue by Item Client Month - Power BI'[Client], 1 )

RETURN

SUM ('RFS Invoice Revenue by Item Client Month - Power BI'[Item Total])

- CALCULATE (

                SUM ('RFS Invoice Revenue by Item Client Month - Power BI'[Item Total]),

                FILTER (

                                ALL ('RFS Invoice Revenue by Item Client Month - Power BI'),

                                'RFS Invoice Revenue by Item Client Month - Power BI'[Item Year] = currentYear

                                && 'RFS Invoice Revenue by Item Client Month - Power BI'[Item Month]

                                = currentMonth - 1

                                && 'RFS Invoice Revenue by Item Client Month - Power BI'[Client]) = currentClient)

[v-ljerr-msft · Accepted solution]

Hi @markefrody,

The formula below should work. 

Last Month =
VAR currentYear =
    MAX ( 'RFS Invoice Revenue by Item Client Month - Power BI'[Item Year] )
VAR currentMonth =
    MAX ( 'RFS Invoice Revenue by Item Client Month - Power BI'[Item Month] )
VAR currentClient =
    FIRSTNONBLANK (
        'RFS Invoice Revenue by Item Client Month - Power BI'[Client],
        1
    )
RETURN
    CALCULATE (
        SUM ( 'RFS Invoice Revenue by Item Client Month - Power BI'[Item Total] ),
        FILTER (
            ALL ( 'RFS Invoice Revenue by Item Client Month - Power BI' ),
            'RFS Invoice Revenue by Item Client Month - Power BI'[Item Year] = currentYear
                && 'RFS Invoice Revenue by Item Client Month - Power BI'[Item Month]
                = currentMonth - 1
                && 'RFS Invoice Revenue by Item Client Month - Power BI'[Client] = currentClient
        )
    )

Regards

[Anonymous]

Hi, 

I tried this formula, it works and able to calculate difference between months in the current year. 

However, this is not workable for compare different year different month such as comparing Jan 2018 and Dec 2017. the Jan 2018 unable to detect the year 2017 data

[markefrody]

@v-ljerr-msft Thank you! Works perfectly! 🙂