Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
@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.