cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 =
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 ACCEPTED SOLUTION
Super User

@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 =

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.

Super User

@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 =

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors