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.
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
Solved! Go to 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
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
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)
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
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
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |