Solved

Saving calculation

  • 5 February 2024
  • 3 replies
  • 150 views

Userlevel 3
Badge +6
  • Newly Minted Pigmenteer
  • 21 replies

Hi! Can someone please help with setting up the formula for below? Thanks in advance!

----

Dates:

Given Data (yellow highlighted)

 

Question: I am seeing for formula to get the result below yellow highlighted row, “Result”. 

 The calculation starts from 3rd months from the start date, 10/31/2022. So it needs to calculate “January value 18,792 minus start date value 18,792. The result for 2023 Jan is zero. Then, calculation is same concept but moves forward. It’s always deducting the start date value, 18,792.  For July 2023, calculation is July 2023 19,355 minus the start date value 18,792, resulting 564.

The calculation should stop in Bridge end date, 12/31/2023. 

 

 

Thank you!

icon

Best answer by Stephen Phan 7 February 2024, 22:48

View original

3 replies

Userlevel 2
Badge +3

When I want to reference a singular value but applied across multiple dimensions (in your case, a Month dimension) I like to use [by constant:]. Since you have a defined start date in which your value will be subtracted from, you could try something like

Metric_foo -
(
Metric_foo
[filter: Month.‘End Date’ = date(2022,10,31)] //choose which date your metric value is fetching
[remove: Month] //temporarily remove Month from Metric_foo’s metric structure
[by constant: Month] //apply that fetched value to each month
)
[filter: Month.‘End Date’ <= date(2023,12,31)]


To make it more dynamic you could filter instead on a property of Month, like a “Bridge_Is_Valid” boolean defined by start and end dates. Exact syntax would change depending on if your date input is a dimension or a date data type.

if(
Month.'End Date' >= Bridge_Run_Rate_Start_Date and Month.'End Date' <= Bridge_Run_Rate_End_Date.'Start Date'
, TRUE
, FALSE)

 

Userlevel 3
Badge +6

 Thanks @Stephen Phan  for assisting. 

 

I think the formula is almost there but having hard time using the Boolean metric I newely created.

IF( 'Bridge Start End Boolean','EE_Base+Bonus (History)' - ('EE_Base+Bonus (History)'[FILTER: Month.'End Date' = 'Bridge Start End Boolean'][REMOVE: Month])[BY CONSTANT: Month])

How should I update for the ‘[FILTER: Month.'End Date' = 'Bridge Start End Boolean']’ part?

 

Thank you!

Userlevel 3
Badge +6

Just figured it out! Thank you!

 

IF( 'Bridge Start End Boolean','EE_Base+Bonus (History)' - ('EE_Base+Bonus (History)'[FILTER: Month.'End Date' = 'Bridge Run Rate Start Date' ][REMOVE: Month])[BY CONSTANT: Month])

 

Reply