Chance Insight – Data Analytics

DAX – Creating Multiple Measure With DAX Query Editor

—

by

CB
in

Creating multiple measures in DAX Query Editor

It may be useful to create templates for creating DAX models, using commonly used DAX measures. Here we create a template for commonly used sales measures, such as Sales YTD, Sales Last Year, Sales Year on Year, and Sales Year on Year %, we can then apply the same logic for QTD and MTD.

The full code for creating the measures, I will add to the bottom of this page.

For this example, I am using just 2 tables from the Contoso Retail Data warehouse: DimDate and FactSales. They are joined on the DateKey.

We start with the Year sales measures as shown below in the DAX query editor. To add them to the model, we just click ‘Update model: Add new measure’, but first we want to format the code, using the Format Query button.

Here is the code, with the DAX formatted.

We can then click the 4 ‘Update mode: Add new measure’ texts and it will add the 4 measures to the model..


We can then create similar measures for QTD and MTD as follows:

Here is the code for the Quarterly measures:

The code for creating the Monthly measures is as follows:

That gives me the 12 measures in record time!


As promised here is the full code that can be copied and pasted. Of course, you’ll need to change the table names as required. Note, I have created an empty ‘_Meusures’ table to act as a container for the measures.

// Learn more about DAX queries at https://aka.ms/dax-queries
DEFINE
//Year Measures
	MEASURE '_Measures'[Sales_YTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESYTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_YTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESYTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_YOY] = '_Measures'[Sales_YTD] - '_Measures'[Sales_LY_YTD]
	MEASURE '_Measures'[Sales_YOY%] = ('_Measures'[Sales_YTD] - '_Measures'[Sales_LY_YTD]) / '_Measures'[Sales_LY_YTD]
	
//QTD Measures
	MEASURE '_Measures'[Sales_QTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESQTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_QTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESQTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_QTD_YOY] = '_Measures'[Sales_QTD] - '_Measures'[Sales_LY_QTD]
	MEASURE '_Measures'[Sales_QTD_YOY%] = ('_Measures'[Sales_QTD] - '_Measures'[Sales_LY_QTD]) / '_Measures'[Sales_LY_QTD]
	
	//MTD Measures
	MEASURE '_Measures'[Sales_MTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			DATESMTD('DimDate'[Datekey])
		)
	MEASURE '_Measures'[Sales_LY_MTD] = CALCULATE(
			SUM(FactSales[SalesAmount]),
			SAMEPERIODLASTYEAR(DATESMTD('DimDate'[Datekey]))
		)
	MEASURE '_Measures'[Sales_MTD_YOY] = '_Measures'[Sales_MTD] - '_Measures'[Sales_LY_MTD]
	MEASURE '_Measures'[Sales_MTD_YOY%] = ('_Measures'[Sales_MTD] - '_Measures'[Sales_LY_MTD]) / '_Measures'[Sales_LY_MTD]