Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Formula to compare numbers based on intervals and specific dates.

We need to compare the budget figures and the actual revenue. In relation with this, we have the following problems:

- The budget is based on an interval monthly.

- The actual revenue is based on specific dates.

 

How can we compare the columns?

 

Additionally, we would like to make a column accumulating the budget figures. So we will be able to identify wether the budget is achieved or not.

 

Budget:

 

Revenue:

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    To create a calculated column using the formula in Budget Table.

     

    Column = var actual = CALCULATE(SUM(Revenue[Amount]),FILTER(ALL(Revenue),Revenue[Date]>=Budget[FromDate] && 'Revenue'[Date]<=Budget[ToDate]))
return
IF(ISBLANK(actual),BLANK(),IF(actual>='Budget'[AmountDefaultCurrency],"achieved","not achieved"))

     

    For more details, please check the pbix as attched.

     

    Regards,

    Frank

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous,

     

    To create a calculated column using the formula in Budget Table.

     

    Column = var actual = CALCULATE(SUM(Revenue[Amount]),FILTER(ALL(Revenue),Revenue[Date]>=Budget[FromDate] && 'Revenue'[Date]<=Budget[ToDate]))
return
IF(ISBLANK(actual),BLANK(),IF(actual>='Budget'[AmountDefaultCurrency],"achieved","not achieved"))

     

    For more details, please check the pbix as attched.

     

    Regards,

    Frank

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous,

     

    Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

     

    Regards,
    Frank