Need help on a Row level data Calculation

[smartp121]

Hi Friends,

I need some help with this calculation, Don't know how to make this work

I have data in the following format 

Year	Month	Revenue	MTD Revenue
2020	4	489,611
2020	5	308,211	31,404
2020	6	277,696	991
2020	7	277,696	20,907
2020	8	284,882	28,899
2020	9	274,322	5,106

The MTD Revenue calculation should be like Month month 5 - Month 4 = month 5 MTD, similar to the remaining months.

please help me with this calculation.

[v-yangliu-msft]

Hi   @smartp121 ,

It should have nothing to do with TotalDirectRevenue. This error is mainly EARLIER('Projects’[Revenue Month]), Parameter is not the correct type.

You can see whether MTD_Revee uses a calculated column or a measure, because the EARLIER function cannot be used for measure, and an error will occur

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @smartp121,

Are you using a calculated column or a measure?

For measure, it is not possible to use the earlier function

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smartp121]

Hi @v-yangliu-msft ,

The column Projects'[TotalDirectRevenue] is coming from the DB, I renamed it to PTD Direct Revenue Recognized at the report level,

it's not a measure or calculated column

its Datatype in the DB is Float 

[v-yangliu-msft]

Hi  @smartp121 ,

Here are the steps you can follow：

1. Create calculated column.

MTD_Revenue =
var _mtd=
CALCULATE(SUM('Table (2)'[PTD Direct Revenue Recognized]),FILTER('Table (2)','Table (2)'[Month]=EARLIER('Table (2)'[Month])))
-
CALCULATE(SUM('Table (2)'[PTD Direct Revenue Recognized]),FILTER('Table (2)','Table (2)'[Month]=EARLIER('Table (2)'[Month])-1))

return IF('Table (2)'[Month]=4,'Table (2)'[PTD Direct Revenue Recognized],_mtd)

MTD_HXC =
var _mtd=
CALCULATE(SUM('Table (2)'[PTD HxC]),FILTER('Table (2)','Table (2)'[Month]=EARLIER('Table (2)'[Month])))
-
CALCULATE(SUM('Table (2)'[PTD HxC]),FILTER('Table (2)','Table (2)'[Month]=EARLIER('Table (2)'[Month])-1))

return IF('Table (2)'[Month]=4,BLANK(),_mtd)

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smartp121]

Hi @v-yangliu-msft  (Liu)

Thanks for the calculation , I'm getting below error, any help pls 

[v-yangliu-msft]

Hi  @smartp121 ,

According to your calculation rules, The MTD Revenue calculation should be like Month month 5-Month 4 = month 5 MTD.

The situation should look like the following table:

For example, May’s MTD Revenue should be -181400, but your MTD Revenue is 31404.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smartp121]

@v-yangliu-msft 

you can see the calculation in the fig above 

MTD Revenue is D7-D6 and I need to calculate MTD Revenue column, ( Column J)

[smartp121]

Hi @camargos,

sorry, I missed explaining something,

Column D should be my Result 

This report has a filter on Year so when I put a filter on Year the Column D should populate automatically based on calculation.

Year	Month	Revenue	MTD Revenue
2020	4	489,611
2020	5	308,211	31,404
2020	6	277,696	991
2020	7	277,696	20,907
2020	8	284,882	28,899
2020	9	274,322	5,106

[camargos88]

@smartp121 ,

Check the attached file.

[camargos88]

@smartp121 ,

Can you explain more the logic here ? 

[smartp121]

@camargos88  the issue is to identify the calculation logic to calculate the MTD Revenue, 

The MTD Revenue of Month 5  = Reven of Month 5 - Revenue of Month 6

how do I put this together in a calculation how to subtract row data ?