Forum Discussion
Calculate Target Based on Current Date and Date Table
Hi,
I have a simple question but cannot seem to find a match on the forums. My company has different branches and we set spending targets for the branch's budget based on a daily target rate. The target rates are in a separate column in a custom Date table which is marked as a Date table.
I believe I need a measure to calculate the Target Budget spent amount based on the current day. So for example if today is 12/01 and we're looking at the Logistics department, the Target Spent = [Logistics Budget] * 0.054. If it was 12/5 it would be Target Spent = [Logistics Budget] * 0.061
I've tried to solve this with a calculated column however my calculated column only works on the overall company budget and doesn't properly slice when groups try to interact with their report and see the different department budgets.
| Date | Target | Fiscal Year QTR |
| 12/1/2023 | 0.054 | Q1 |
| 12/2/2023 | 0.056 | Q1 |
| 12/3/2023 | 0.057 | Q1 |
| 12/4/2023 | 0.059 | Q1 |
| 12/5/2023 | 0.061 | Q1 |
| 12/6/2023 | 0.063 | Q1 |
| 12/7/2023 | 0.064533333 | Q1 |
| 12/8/2023 | 0.066304762 | Q1 |
| 12/9/2023 | 0.06807619 | Q1 |
| 12/10/2023 | 0.069847619 | Q1 |
| 12/11/2023 | 0.071619048 | Q1 |
| 12/12/2023 | 0.073390476 | Q1 |
| 12/13/2023 | 0.075161905 | Q1 |
| 12/14/2023 | 0.076933333 | Q1 |
| 12/15/2023 | 0.078704762 | Q1 |
| 12/16/2023 | 0.08047619 | Q1 |
| 12/17/2023 | 0.082247619 | Q1 |
| 12/18/2023 | 0.084019048 | Q1 |
| 12/19/2023 | 0.085790476 | Q1 |
| 12/20/2023 | 0.087561905 | Q1 |
| 12/21/2023 | 0.089333333 | Q1 |
| 12/22/2023 | 0.091104762 | Q1 |
| 12/23/2023 | 0.09287619 | Q1 |
| 12/24/2023 | 0.094647619 | Q1 |
| 12/25/2023 | 0.096419048 | Q1 |
| 12/26/2023 | 0.098190476 | Q1 |
| 12/27/2023 | 0.099961905 | Q1 |
| 12/28/2023 | 0.101733333 | Q1 |
| 12/29/2023 | 0.103504762 | Q1 |
| 12/30/2023 | 0.10527619 | Q1 |
| 12/31/2023 | 0.107047619 | Q1 |
| 1/1/2024 | 0.108819048 | Q2 |
| 1/2/2024 | 0.110590476 | Q2 |
| 1/3/2024 | 0.112361905 | Q2 |
| 1/4/2024 | 0.114133333 | Q2 |
| 1/5/2024 | 0.115904762 | Q2 |
| 1/6/2024 | 0.11767619 | Q2 |
| 1/7/2024 | 0.119447619 | Q2 |
| 1/8/2024 | 0.121219048 | Q2 |
| 1/9/2024 | 0.122990476 | Q2 |
| 1/10/2024 | 0.124761905 | Q2 |
| 1/11/2024 | 0.126533333 | Q2 |
| 1/12/2024 | 0.128304762 | Q2 |
| 1/13/2024 | 0.13007619 | Q2 |
| 1/14/2024 | 0.131847619 | Q2 |
| 1/15/2024 | 0.133619048 | Q2 |
| 1/16/2024 | 0.135390476 | Q2 |
| 1/17/2024 | 0.137161905 | Q2 |
2 Replies
darlingent
You can create a measure as follows, assuming you already have measure to calculate the Logistics BudgeTarget Spent = [Logistics Budget] * SELECTEDVALUE ( DateTable[Target] )This measure uses SUMX, which iterates the Dates table and multiplies Logistics Budget by the daily target rate:
Target Spent = SUMX ( Dates, [Logistics Budget] * Dates[Target] )
