Forum Discussion

Anonymous

Not applicable

9 years ago

Forecasting sales using measures but getting same total for all dates?

I'm using a measure for each period; CEarnings (Prior -28) = SUMX( CALCULATETABLE(ICPlacements, DATEADD(ALL(DateTable[Date]),-28,DAY), ALL(DateTable[Date])), ICPlacements[Invoiced NFI]) ...

Icon for Microsoft Employee rank

Microsoft Employee

9 years ago

Hi Anonymous,

You're using ALL function which returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied in your measure. So the measure get same value for all your dates.

Could your try removing ALL functions in your measure to see if it works? :smileyhappy:

CEarnings (Prior -28) =
SUMX (
    CALCULATETABLE ( ICPlacements, DATEADD ( DateTable[Date], -28, DAY ) ),
    ICPlacements[Invoiced NFI]
)

Regards

Anonymous
Not applicable
9 years ago
Much improved, thanks. But I can't seem to get the dates to push on into the future

As per screenshot below tables I have a dates table with dates up to 2018 but the datestable created off of it only goes up to June 2017?

DateTable =
ADDCOLUMNS (
CALENDAR (MINX(Dates,[Date]), NOW()),
"Year", YEAR ( [Date] ),
"QuarterOfYear", FORMAT ( [Date], "Q" ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"DateInt", FORMAT ( [Date], "YYYYMMDD" ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ))

I followed this link http://fountainanalytics.com/creating-simple-time-series-forecasting-using-microsoft-powerbi-and-dax/ in attempt to plot a forecast of sales.

The black line and trend formula below isn't working. Perhaps I'm not using the correct method to produce a forecast for years, months and weeks?

Related content