Calculate previous year revenue for the customers who were not present in system currently

[unnipbi123]

Hello All,

I have a revenue table in my report which will have MoM revenue for all customers. out of this i have created a calculated table using the formula below.

Annual NRR = SUMMARIZE(Revenue,Revenue[date],Revenue[Reseller Name],"Revenue", sum(Revenue[amount]))

In the calculated table, i am calculating revenue 11 months back for each customer using the below formula.

Revenue_PY = CALCULATE(SUM(Revenue[amount]), filter(Revenue,Revenue[Reseller Name]='Annual NRR'[Reseller Name] && Revenue[date]=dateadd('Annual NRR'[date],-11,MONTH)))

i have created a matrix visual to show my data MoM as below.

	Feb	Mar	Apr
Reseller	Revenue	Revenue_Last year	Revenue	Revenue_Last year	Revenue	Revenue_Last year
ABC	3342	36835	3342	36835	3342	36835
XYZ	20805	35599	20805	35599	20805	35599
RSTY	419	14207	419	14207	419	14207
KJLI	10041	18257	10041	18257	10041	18257

Problem here is, there are few customers who had revenue till last year but now been removed from the system. for example, customer PGA had revenue on Apr-2021 but after that they removed from the system. So on the report this customer should appear on Mar-2022 data as below.

	Mar
Reseller	Revenue	Revenue_Last year
PGA		20000

Since this customer is removed after Apr-2021, its will not satisfy the condition "Revenue[Reseller Name]='Annual NRR'[Reseller Name] && Revenue[date]=dateadd('Annual NRR'[date],-11,MONTH)". Because of this i am not able to see this customer in Mar-22 data even though they have created revenue last year. 

Please advise is there a way we can show the customers who had revenue 11 months back even though they have not present in the system now.

[v-yalanwu-msft · Accepted solution]

Hi, @unnipbi123 ;

You must create a new table with Mar-2022 (or add a new column with an extension of 11 months), i.e. the original date column is extended 11 months back, because if you are in the original table, its date column is only up to Apr-2021, and it will not show dates that do not exist.

So you can create a column

Column = EOMONTH([Date],11)

Or a new table

Table = SUMMARIZE(SUMMARIZE('Revenue',[Date],"date1",EOMONTH([Date],11)),[date1])

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.