Forum Discussion

dnaman's avatar
dnaman
Helper I
8 years ago
Solved

Calculate rate based on dates and expected value

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

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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())

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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())