Formula to compare numbers based on intervals and specific dates.

[Anonymous]

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:

[v-frfei-msft]

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

[v-frfei-msft · Accepted solution]

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