Calculate rate based on dates and expected value

[dnaman]

Hi All,

I would like to create a new column/measure, called "Revenue/Month" that is based on 3 columns in my table:

Weighted Value

Revenue Start Date

Revenue End Date

It should be weighted value / total # of months between the two dates

I've looked at this forum post here, but i'm having trouble doing it step by step (first, creating a new column to calculate months keeps throwing me errors (column cannot be found..))

https://community.powerbi.com/t5/Developer/Number-of-Months-between-two-dates/m-p/90008

Any help is appreciated.

Basically i want to come up with an expanded data set that lists a row into multiple rows, each differing by the revenue month, and then i will ultimately create a chart showing revenue by month.

Many thanks in advance

[Phil_Seamark · Accepted solution]

Hi @dnaman

Here is a calculated column that might work in DAX

Column = 
VAR StartYear = LEFT('Table1'[Expected Revenue Start Year-Mon],4)
VAR StartMonth = RIGHT('Table1'[Expected Revenue Start Year-Mon],2)

VAR EndYear = LEFT('Table1'[Expected Revenue End Year-Mon],4)
VAR EndMonth = RIGHT('Table1'[Expected Revenue End Year-Mon],2)

VAR MonthsDiff = DATEDIFF(
                        DATE(StartYear,StartMonth,1) ,
                        DATE(EndYear,EndMonth,1) ,
                        MONTH)

RETURN DIVIDE('Table1'[Weighted Value],MonthsDiff,BLANK())

[dnaman]

That works, thanks!