Forum Discussion

PKPK90's avatar
PKPK90
Icon for Helper I rankHelper I
2 years ago

Formula for Projecting Monthly Financial Targets

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

 

 

1 Reply