Calculated column difference between dates

[Anonymous]

Hi all. i have some data that looks like this

DATE REVENUE

August 100

September 150

October 250

This is cumulative and i want to see the difference from the previous month. so it should be

August=100, September = 50, December =100

How do i do that?

[tarunsingla · Accepted solution]

Two things:

First, ensure your DATE field (column) is of Type "Date" (and not Text).

Second, you can create a calculated column using the formula below to get the difference in value.

Difference from PreviousMonth =

VAR earlier_month = CALCULATE(MAX('Table'[DATE]), FILTER('Table', 'Table'[DATE] < EARLIER('Table'[DATE])))

RETURN 'Table'[REVENUE] - LOOKUPVALUE('Table'[REVENUE], 'Table'[DATE], earlier_month)

[Anonymous]

When i do that i get a table of multiple values was supplied when a single value was expected. any thoughts?

[parry2k]

@Anonymous change you date column data type date and enter valid date, may be 1st of every month.

Add date dimension in your model, there are many posts on how to add one

set relationship between date dimension and your transaction table

from here everything will be super easy by adding measure, you can do all this in one measure as well.

Total Revenue = SUM ( Table[Revenue] )

PM Revenue = CALCULATE ( [Total Revenue], PREVIOUSMONTH( DateDimension[Date] ) )

Difference = [Total Revenue] - [PM Revenue]

[Anonymous]

you mean something like that?

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns