Skip to main content

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!

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
bremove: Month] //temporarily remove Month from Metric_foo’s metric structure
eby 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)

 

 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)'oFILTER: Month.'End Date' = 'Bridge Start End Boolean']eREMOVE: Month])nBY CONSTANT: Month])

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

 

Thank you!

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


Ask your question here!

Terms & ConditionsCookie settings