Formula for Projecting Monthly Financial Targets

PKPK90 — Fri, 19 Apr 2024 07:07:32 GMT

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

))

Re: Formula for Projecting Monthly Financial Targets

lbendlin — Sat, 20 Apr 2024 00:25:39 GMT

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-Forum/ba-p/963216

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/1447523

topic Re: Formula for Projecting Monthly Financial Targets in DAX Commands and Tips

Formula for Projecting Monthly Financial Targets

Re: Formula for Projecting Monthly Financial Targets