Repeating rows to create sample data II

Share The Knowledge

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.

20 rows

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))
731 rows of dates

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")
)
Final Date table

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:

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.


Share The Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *