Intelligent Workbook for Generating Basic DAX Formulas used for Time Series Analysis of Measures

• Download Intelligent_DAX_Generator_for_Time_Series_Measures_and_Calculations.zip - 3.9 MB

The Problem

Assume you have a lot of measures - Gross Sales, Net Sales, Guest Counts, Discount Amounts, etc.

You need to now generate DAX for time series analysis against these measures, i.e., week to date, month to date, prior year comparisons, etc.

The realization is that the time series DAX queries for the measures would be similar regardless of the measure.

Solution

So, instead of manually having to re-write DAX queries from scratch for each time series analysis, for that measure, this workbook will take basic information about the measure as input and then output the time series dax.

The intelligent spreadsheet uses pre-programmed formulas to output the DAX queries in a different tab. See sample outputs below for the Total Gross Sales (TGS) measure.

Disclaimer

I've attached the "intelligent" workbook for use. This is a work in progress. If some of the formulas are not right, please feel free to update it once it's generated. And send suggestions to me so that I can make the workbook smarter. I would advise you use the queries generated as a starting point for writing your own, instead of relying on it as final.

Also, there are hidden tabs in the workbook that are used for calculations. You can unhide those if you want to go under the hood.

Again thanks. I'm looking forward to suggestions.