Forum Discussion
Need help on a Row level data Calculation
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.
11 Replies
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 ?
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 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.
Anonymous
you can see the calculation in the fig above
MTD Revenue is D7-D6 and I need to calculate MTD Revenue column, ( Column J)
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.
Hi Anonymous (Liu)
Thanks for the calculation , I'm getting below error, any help pls
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.
Hi Anonymous ,
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
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.
