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!
Best answer by Stephen Phan
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.
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.
