cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
markefrody Member
Member

Month on Month Difference of Revenue Per Customer

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 NameDateMonthMonth NameYearRevenue
Ferro Steel Inc.: Los Angeles2017-06-31 00:00:00.0006June2017899.29
Harris Supply Solutions: Seattle2017-06-29 00:00:00.0006June201735
Harris Supply Solutions: Seattle2017-06-29 07:49:00.0006June2017300
USS/POSCO: Pittsburg2017-06-15 06:52:31.0006June2017108
Ferro Steel Inc.: Los Angeles2017-07-07 00:00:00.0007July2017468
Harris Supply Solutions: Seattle2017-07-27 00:00:00.0007July201750
USS/POSCO: Pittsburg2017-07-02 00:00:00.0007July2017790

 

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 NameTotal RevenueDiff last month% Diff last monthTotal RevenueDiff last month% Diff last monthTotal RevenueDiff last month% Diff last month
Client 1100-2010%300200300%50-25017%
Client 2200509%70-13035%600530857%
Client 33006025%75-22525%10025133%


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

Appreciate all your kind advice and support.


Best regards,

Mark

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Month on Month Difference of Revenue Per Customer

Hi @markefrody,

 

The formula below should work. Smiley Happy

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

5 REPLIES 5
v-ljerr-msft Super Contributor
Super Contributor

Re: Month on Month Difference of Revenue Per Customer

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. Smiley Happy

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 Member
Member

Re: Month on Month Difference of Revenue Per Customer

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 Super Contributor
Super Contributor

Re: Month on Month Difference of Revenue Per Customer

Hi @markefrody,

 

The formula below should work. Smiley Happy

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

markefrody Member
Member

Re: Month on Month Difference of Revenue Per Customer

@v-ljerr-msft Thank you! Works perfectly! :-)

catherinen Occasional Visitor
Occasional Visitor

Re: Month on Month Difference of Revenue Per Customer

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