Solved

Saving calculation

  • 5 February 2024
  • 3 replies
  • 97 views
Y
Rank
Userlevel 3
Badge +6
  • Yaejis Newly Minted Pigmenteer
  • 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

S
Rank
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)

 

Y
Rank
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!

Y
Rank
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


Ask your question here!

Terms & ConditionsCookie settings