topic Need help with a forecast Measure in DAX Commands and Tips

Need help with a forecast Measure

BIlix — Mon, 28 Aug 2023 16:00:15 GMT

I have a forecast measure which should return a value based on a Sum(FactTable[Cost] YTD measure.

A cost value in my fact table is always assigned to the end of a month.

Currently The Forecast only calculates a value for the end of the Year or, if its earlier, until the end of a project.
Here, it calculate the difference from the last refresh column of the fact table until end of project or end of year.

Now I need to adjust the measure to show values not only for the end of the year, but for every end of the month until the end of the year or Project.

For example, I have Months in Row and the Forecast Measure. It should return The YTD Cost measure for every Month until end of August. Afterwards it should display the Forecast measure over all Projects For End of September, October, November, December.

Forecast =

VAR CurrentYear = YEAR(TODAY())

VAR LastDayOfYear = DATE(CurrentYear, 12, 31)

VAR ProjectEndDate = IF(ISBLANK(MAX(DimProject[Planned Finish])), LastDayOfYear, MAX(DimProject[Planned Finish]))

VAR DaysPassed = IF(ProjectEndDate < DATE(CurrentYear, 1, 1), 0, DATEDIFF(DATE(CurrentYear, 1, 1), ProjectEndDate, DAY))

VAR RemainingDays = IF(ProjectEndDate > LastDayOfYear, 0, DATEDIFF(MAX(Fact[RefreshDate]), ProjectEndDate, DAY))

VAR CurrentActual = [Costs]

VAR AverageCostPerDay = CurrentActual / DaysPassed

VAR ForecastedAmount = [Costs YTD] + (AverageCostPerDay * RemainingDays)

VAR Calc = IF(ProjectEndDate < MAX(Fact[RefreshDate]), CurrentActual, ForecastedAmount)

RETURN

Calc

Thanks in Advance

Re: Need help with a forecast Measure

some_bih — Wed, 30 Aug 2023 06:03:56 GMT

Hi BIlix I suggest you to read, if not already, article and video from link. After that go to your model, and think how to resolve your issue. Hope this help, kudos appreciated.

https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

Re: Need help with a forecast Measure

technolog — Sun, 24 Sep 2023 08:14:26 GMT

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.