cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JINEO Regular Visitor
Regular Visitor

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

 

And then as below a trend measure as below;

 

Forecasting.png

 

 

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

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

Hi @JINEO,

 

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? Smiley Happy

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

Regards

JINEO Regular Visitor
Regular Visitor

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

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?

 

Forecast2.png