Forum Discussion

dnaman's avatar
dnaman
Icon for Helper I rankHelper 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 weigh...
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())
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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())
dnaman's avatar
dnaman
Icon for Helper I rankHelper I
8 years ago

That works, thanks!