Get the MoM revenue data for the customers who are not present on particular month

[unnikrish]

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.

 

[Chihiro]

From quick looks. You don't really need that condition "Revenue[Reseller Name]='Annual NRR'[Reseller Name] . However, you should have Reseller dimension table tied to your table(s) and use the dimension table's Reseller Name as source of your Reseller field in Matrix visual.