0
Project Cost
January 323
Feb 323

I have a table as followed seen above which ROW is month (filtered by a certain project) and values are cost of the project. I want to calcuate the difference between two months, but I am having trouble. How do I subtract two rows from each other. In the code I wrote:

Variance = [Cost] - CALCULATE([Cost],PREVIOUSMONTH('Month'[Month))

I get the following error, A column specified in the call to function is not of type date.

Is there a way to manual subtract two months?

MDL7833
  • 1
  • 3
  • You can do this but then you need to have an order in your data. There is in your ras data now order because the column project is not of type date. Please remember this is not sheet data as excel dictates on a sheet.. If you have a date column giving you the order or an index, then we can help. – Aldert Sep 15 '21 at 18:57

2 Answers2

0

The best way to do it is to replace your month with an actual Date value. The first of the month for example. The you should be able to do something like this assuming your month dates are unique: If they are not unique you should create a Dates table (See Microsoft's Guidance on date table) and join.

variance = [Cost] - Calculate([COST],(PARALLELPERIOD(Month[Month],-1,Month)
Spevy
  • 1,325
  • 9
  • 22
0

You can use EARLIER function here but, only when the Months have an Id. Such as

1 Jan 2 Feb 3 March ...

Link for details

However, I would suggest creating a date table and then having a relationship from the date table to your table. By using date table you can easily achieve using in-buit date functions.

Mr.Batra
  • 787
  • 1
  • 5
  • 11
  • Mr.Batra, the date field that I had was not a true date field. So I create a date using powerbi Date(YR,Month,Day). And I ended up using the Parallelperoid function. Thank you for the help! – MDL7833 Sep 16 '21 at 12:28