Forum Discussion

luisfarantes's avatar
luisfarantes
Frequent Visitor
3 years ago
Solved

Previous Month Revenue Measure not working

Hello people.

 

I'm having trouble with a Measure that shows the previous month Revenue.

 

My table have some calculated columns that brings the final revenue by client, because I needed to make a visualization table that Sums the Final Revenue for each client, but this revenue has a discount based on volume, and if I simply create a measure the total line is going to apply the volume discount to the aggregate volume. So after that I created this Measure:

 

 

Fatura Agenda Final = 
var TabelaTemporaria =
    ADDCOLUMNS(
        SUMMARIZE('AP', 'AP'[participante e detentor],'AP'[Nome Cobrado], AP[data].[Mês]),
        "Distinct", CALCULATE(MAX('AP'[Fatura Final - Agenda]))
    )
return
SUMX(TabelaTemporaria, [Distinct])

 

 

 This is the Final Revenue Calculated Column:

 

 

Fatura Final - Agenda = 
IF('AP'[Total agenda] >= 1000000001, 0.1*('AP'[Total agenda]*0.02), 
  IF('AP'[Total agenda] >= 100000001, 0.2*('AP'[Total agenda]*0.02),
   IF('AP'[Total agenda] >= 7000001, 0.275*('AP'[Total agenda]*0.02),
    IF('AP'[Total agenda] >= 1000001, 0.6* ('AP'[Total agenda]*0.02), IF('AP'[Total agenda] >=100001, ('AP' [Total agenda]*0.02) * 0.75, 
     ('AP'[Total agenda]*0.02))))))

 

 

 And this is the Total Agenda Calculated Column:

 

 

Total Agenda =
CALCULATE(SUM('AP'[agendas online]), FILTER('AP', 'AP'[participante e detentor] = EARLIER('AP'[participante e detentor])), FILTER('AP', 'AP'[data].[Mês] = EARLIER('AP'[data].[Mês]))) + CALCULATE(SUM('AP'[agendas batch]), FILTER('AP', 'AP'[participante e detentor] = EARLIER('AP'[participante e detentor])), FILTER('AP', 'AP'[data].[Mês] = EARLIER('AP'[data].[Mês])))

 

 

 

I tried many ways to bring the last month revenue, but none of them worked.

This one the value is 0:

 

 

Fatura Agenda t-1 = 
CALCULATE([Fatura Agenda Final], PREVIOUSMONTH(AP[data]))

 

 

 And this one brings me the same month Revenue:

 

 

Fatura Agenda t-1 = 
CALCULATE([Fatura Agenda Final], FILTER(AP,PREVIOUSMONTH(AP[data])))

 

 

Does any one knows how can I make it work?

 

Thanks! 

1 Reply