Formula for Projecting Monthly Financial Targets

[PKPK90]

Hello, I'm trying to create a formula that will show the AmountUSD for past dates within the current month (the table only has data up to today). For the remaining days of the month, I want to display the average (TARGET - (Total Amount USD)) / remaining days in the month.

Currently, the formula works only partially correctly. It displays the AmountUSD, but it also shows the average for past data, which I don't want. The future data should show how much we need to send on average per day to achieve the target.

I have 3 tables.

Calendar = CALENDAR(MIN('sil CDM_GL'[Transaction_Date]),EOMONTH(TODAY(),0))

sil CDM_GL with Site, Date and AmountUSD

Target with Site and Total (target)

Calendar is connected to sil CDM_GL on Date = Date

Target is connected to sil CDM_GL by Site

M_AmountUSD =

CALCULATE(

    SUM('sil CDM_GL'[Amount(USD)]),

    RELATEDTABLE('Calendar')

)

+

VAR TodayDate = TODAY()

VAR StartOfMonth_ = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

VAR EndOfMonth_ = EOMONTH(TodayDate, 0)

VAR TotalAmountThisMonth =

    CALCULATE(

        SUM('sil CDM_GL'[Amount(USD)]),

        DATESBETWEEN(

            'Calendar'[Date],

            StartOfMonth_,

            EndOfMonth_

        )

    )

VAR TargetTotal =

    CALCULATE(

        SUM('Target'[Total]),

        DATESBETWEEN(

            'Calendar'[Date],

            StartOfMonth_,

            EndOfMonth_

        )

    )

VAR DaysInMonth =

    DAY(EndOfMonth_)

VAR DaysRemaining =

    DaysInMonth - DAY(TodayDate)

RETURN

    IF(SELECTEDVALUE('Calendar'[Check])=TRUE(),0,

    IF(

        DaysRemaining > 0,

        DIVIDE(TargetTotal - TotalAmountThisMonth, DaysRemaining, BLANK()),

        0

    ))

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...