Let’s Get Setup!

This lab comes with two Power BI Desktop files. The first one just has the data and the basics set up for you do do the labs.

The second file has all the answers, if you can stock use the second file to help you work through the assignements.

Sales Forecast - Trend = 
VAR ActualsStartDate = 
CALCULATE(MINX(FILTER(ADDCOLUMNS('Date', "_Revenue", [Revenue]), [_Revenue]<>BLANK()), [Date]), ALL('Date'))

VAR ActualsEndDate = 
CALCULATE(MAXX(FILTER(ADDCOLUMNS('Date', "_Revenue", [Revenue]), [_Revenue]<>BLANK()), [Date]), ALL('Date'))

VAR SelecteWeekIndex = 
CALCULATE
(
	DISTINCTCOUNT('Date'[Week Start Date]),
	 FILTER
	 (
		ALL('Date'), 
		'Date'[Date]<=MAX('Date'[Date]) && 'Date'[Date]>=ActualsStartDate
	)
)
       
VAR WeeklyData = 
    FILTER(
		CALCULATETABLE(
			ADDCOLUMNS(
        		VALUES('Date'[Week Start Date]),
        		"Revenue", [Revenue],
        		"WeekIndex", DATEDIFF(ActualsStartDate, 'Date'[Week Start Date], WEEK) + 1), ALL('Date')
    	),
		[Revenue]<>BLANK())
		
VAR Stats = 
    LINESTX(
        WeeklyData,
		[Revenue], 
		[WeekIndex]
    )
	
VAR Slope = MAXX(Stats, [Slope1])

VAR Intercept = 
SELECTCOLUMNS(Stats, "Intercept", [Intercept])

RETURN Slope * SelecteWeekIndex + Intercept