Microsoft Power BI Cookbook

Regression table and measures

1. From the Modeling tab in Power BI Desktop, click New Table.
2. Create a table named MktSalesRegression which retrieves the X and Y variables at the monthly grain.

MktSalesRegression = FILTER(SUMMARIZECOLUMNS('Date'[Calendar Yr-Mo],'Date'[Calendar Year Month Number],CALCULATETABLE('Date','Date'[Calendar Month Status] <> "Current Calendar Month"),"Marketing Amount", [Marketing Fin Amount],"Internet Sales", [Internet Net Sales]),NOT(ISBLANK([Internet Sales]) || ISBLANK([Marketing Amount])))

SUMMARIZECOLUMNS() groups the table at the monthly grain and FILTER() removes any rows (months) which don't have both internet sales and marketing values. CALCULATETABLE() passes a filtered date table to SUMMARIZECOLUMNS() to ...