topic Re: Calculating Yearly Forecast and show it by month in a bar chart in DAX Commands and Tips

Calculating Yearly Forecast and show it by month in a bar chart

Gabiiiii — Thu, 11 Jul 2024 08:18:05 GMT

I need to create a bar chart showing the Yearly Forecast for each month -

x axis: Month

y axis: Forecast

Forecast = Actual Cost from Jan to previous month + Budget from this month to Dec.

The bar chart should show the yearly forecast calculated on monthly basis for both historically and prospectively

How to write the DAX function for it? Thank you!!

Re: Calculating Yearly Forecast and show it by month in a bar chart

bhanu_gautam — Thu, 11 Jul 2024 08:27:13 GMT

Gabiiiii ,First create a measure for Actual Cost Up

ActualCostToDate =
CALCULATE(
SUM(Financials[ActualCost]),
DATESBETWEEN(
'Date'[Date],
DATE(YEAR(TODAY()), 1, 1),
EOMONTH(TODAY(), -1)
)
)

BudgetCostToEndOfYear =
CALCULATE(
SUM(Financials[BudgetCost]),
DATESBETWEEN(
'Date'[Date],
EOMONTH(TODAY(), 0) + 1,
DATE(YEAR(TODAY()), 12, 31)
)
)

YearlyForecast = [ActualCostToDate] + [BudgetCostToEndOfYear]

Use Yearly Forecast measure in Y axis

Re: Calculating Yearly Forecast and show it by month in a bar chart

Gabiiiii — Thu, 11 Jul 2024 08:56:53 GMT

Hi bhanu, really appreciate your reply! However, I have tried similar approach, it will only show the month-on-month value if I use month as the x axis. I need to show the yearly value for each month,

for example:

Jan: Budget from Jan-Dec

Feb: Actual for Jan + Budget from Feb-Dec

...

Also, I do not have a date column to use, only a single "month“ column in text format

I've tried with AllExcept function, it's also not working as expected...