Hi,
I wonder if you can help please.
I need to get the total revenue per company for the last 2 months and an additional column with their cumulative value over the last 3 years, so we can mark them as they have reached a specific threshold. The challenge is that the years might be different for each company.
Company | Revenue | Date |
A | $800 | 01/01/2023 |
A | $200 | 01/02/2023 |
A | $500 | 01/08/2019 |
A | $50 | 01/05/2021 |
A | $50 | 01/09/2021 |
B | $300 | 01/01/2023 |
B | $100 | 01/03/2019 |
B | $50 | 01/05/2019 |
B | $50 | 01/12/2019 |
B | $1,000 | 01/07/2021 |
B | $3,000 | 01/03/2017 |
C | $2,000 | 01/02/2023 |
D | $200 | 01/01/2022 |
D | $300 | 01/04/2022 |
D | $1,500 | 01/12/2019 |
Please see below what I am trying to achieve:
Company | Company Type | Country | Revenue last 2 months | Cumulative revenue over 3 years |
A | Large | USA | $1,000 | $1,600 |
B | SME | China | $300 | $1,500 |
C | Large | France | $2,000 | $2,000 |
I tried by creating two virtual tables.
First virtual table (Cumulative Revenue 1) to get the main columns using the DAX below:
Cumulative Revenue 1 =
CALCULATETABLE(
SUMMARIZE(
'FACT-Revenue',
ROLLUP( 'FACT-Revenue'[Company],'FACT-Revenue'[YEAR]),
"Total Revenue by Year", SUM('FACT-Revenue'[Revenue])
),
'FACT-Revenue'[date] >= EDATE(TODAY(),-2)
)
So, I managed to get this:
Company | Total Revenue by Year | Year |
A | $1,000.00 | 2023 |
A | $500.00 | 2019 |
A | $100.00 | 2021 |
B | $300.00 | 2023 |
B | $200.00 | 2019 |
B | $1,000.00 | 2021 |
B | $3,000.00 | 2017 |
C | $2,000.00 | 2023 |
D | 500 | 2022 |
D | $1,500.00 | 2019 |
And a second table to create a rownumber with the Rankx function, but for some reason the result in Row Number is always 1.
Cumulative Revenue 2 =
CALCULATETABLE(
ADDCOLUMNS('Cumulative Revenue 1',
"ROW NUMBER",
RANKX(ALLSELECTED('Cumulative Revenue 1'[Company]),
'Cumulative Revenue 1'[YEAR])
),
NOT(ISBLANK('Cumulative Revenue 1'[YEAR]))
)
I would like to get a final table like this:
Company | Total Revenue by Year | Year | RowNumber |
A | $1,000.00 | 2023 | 1 |
A | $500.00 | 2019 | 3 |
A | $100.00 | 2021 | 2 |
B | $300.00 | 2023 | 1 |
B | $200.00 | 2019 | 3 |
B | $1,000.00 | 2021 | 2 |
B | $3,000.00 | 2017 | 4 |
C | $2,000.00 | 2023 | 1 |
D | 500 | 2022 | 1 |
D | $1,500.00 | 2019 | 2 |
My idea is to use the second table (Cumulative Revenue 2) and use Row Numbers 1 to 3 for each company and do a SUM to get the column "Cumulative revenue over 3 years"
Is there an easier way to get this with one table or in one step, or any help to find the error in the second virtual table, please?
Thanks.
