Previous Month Revenue Measure not working

[luisfarantes]

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! 

[amitchandak · Accepted solution]

@luisfarantes , Always use date table, joined with your date table for such cases 

example

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

Fatura Agenda t-1 =
CALCULATE([Fatura Agenda Final], dateadd(Date[Date],-1, month))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.