I found another interesting and easier way to GENERATE sample data using DAX!
Requirement
A fact (aka transaction) table called Sales with 3 fields:
- Date
- CustomerID
- SalesAmount
A Date table with all dates in 2019 & 2020 (based on Sales table).
I used this approach to create tables in my last post. I’ll explain the DAX.
Files
DAX creates Sales Table
Final DAX Code
If you learn by playing around with it instead of reading then copy/paste this into a new table. Otherwise I’ll explain the steps below
Sales =
VAR tablevalues1 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),1),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(98,100,1),
"CustomerID",[Value]
)
)
VAR tablevalues2 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2020,3,25),DATE(2020,5,22),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(100,102,1),
"CustomerID",[Value]
)
)
RETURN
ADDCOLUMNS(
UNION(tablevalues1,tablevalues2),
"Sale Amount",ROUND(WEEKDAY([Date])+([CustomerID]/8),0)
)
Step1 GENERATESERIES
It’s arguments are: Start value, End value, Increment value.
Step 1 GENERATESERIES = GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),3)
It only creates 20 rows due to increment value of 3. Adjust as necessary.
Step2 SELECTCOLUMNS
SELECTCOLUMNS function lets me rename column Value to Date.
Step 2 SELECTCOLUMNS =
SELECTCOLUMNS(
GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),3),
"Date", [Value]
)
Step3 GENERATE
I added a 2nd table (creates 3 CustomerIDs) and then GENERATE creates all combinations from the two tables. 53 rows X 3 rows = 159 rows.
Step 3 GENERATE =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(98,100,1),
"CustomerID",[Value]
)
)
Step4 GENERATE, Variables, UNION
A 2nd GENERATE (different date ranges), converted them into variables, and UNION to stack these two tables. 199 rows.
Step 4 GENERATE, Variables, UNION =
VAR tablevalues1 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(98,100,1),
"CustomerID",[Value]
)
)
VAR tablevalues2 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2020,3,25),DATE(2020,5,22),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(101,102,1),
"CustomerID",[Value]
)
)
RETURN
UNION(tablevalues1,tablevalues2)
Step5 ADDCOLUMNS
I created ‘Sales Amount’ using: ROUND(WEEKDAY([Date])+([CustomerID]/8),0)
Step 5 ADDCOLUMNS =
VAR tablevalues1 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2019,10,27),DATE(2020,4,1),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(98,100,1),
"CustomerID",[Value]
)
)
VAR tablevalues2 =
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(DATE(2020,3,25),DATE(2020,5,22),3),
"Date", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(101,102,1),
"CustomerID",[Value]
)
)
RETURN
ADDCOLUMNS(
UNION(tablevalues1,tablevalues2),
"Sale Amount",ROUND(WEEKDAY([Date])+([CustomerID]/8),0)
)
DAX creates Date Table
Basic yet dynamic Date table based on Sales table above. It has 4 columns.
Final DAX Code
Date =
VAR MinYear = YEAR ( MIN ( Sales[Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Date] ) )
RETURN
ADDCOLUMNS(
CALENDAR(DATE(MinYear,1,1),DATE(MaxYear,12,31)),
"Year", YEAR([Date]),
"Month Nmbr", MONTH([Date]),
"Month Name", Format([Date],"mmm")
)
Date Step1 CALENDAR
CALENDAR function creates a series of dates incorporating the variable values that get min and max values from Sales table.
Date Step 1 = VAR MinYear = YEAR ( MIN ( Sales[Date] ) ) VAR MaxYear = YEAR ( MAX ( Sales[Date] ) ) RETURN CALENDAR(DATE(MinYear,1,1),DATE(MaxYear,12,31))
Date Step2 ADDCOLUMNS
Wrapping CALENDAR with ADDCOLUMNS function lets me add columns.
Date Step 1 =
VAR MinYear = YEAR ( MIN ( Sales[Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Date] ) )
RETURN
ADDCOLUMNS(
CALENDAR(DATE(MinYear,1,1),DATE(MaxYear,12,31)),
"Year", YEAR([Date]),
"Month Nmbr", MONTH([Date]),
"Month Name", Format([Date],"mmm")
)
More About Date Table
Why does date table have have more rows (731) than Sales table (199)?
It’s rare but it’s possible. An official date table has certain requirements. It must contain all days of the year for the minimum Sale table year and all days of the year for the maximum Sale table year (and all dates in between)
Further Learning
Here are some good links to learn more:
- Creating a simple date table in DAX – SQLBI
- Mark as Date table – SQLBI
- GENERATE – DAX Guide
- GENERATESERIES – DAX Guide
- Using GENERATE and ROW instead of ADDCOLUMNS in DAX – SQLBI
Summary
It was fun playing with the GENERATE function. It’s easier to use DAX to create sample data compared to trying to find existing sample data.
I’m so glad that I decided last year to create this blog and practice DAX. Books, videos, courses are all great but I really needed to put the knowledge into practice. I often get stuck but work my way through it.