Calculate Target Based on Current Date and Date Table

[darlingent]

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

[DataInsights]

@darlingent,

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] )

[Fowmy]

@darlingent 

You can create a measure as follows, assuming you already have measure to calculate the Logistics Budge

Target Spent = [Logistics Budget] * SELECTEDVALUE ( DateTable[Target] )