Tyler Bradley

- Background Example

- Basic Strategies

- Better Strategy

- Polishing

- Start out with some data to graph

`month = STARTOFMONTH(orders_small[date])`

`sum_amount = SUM(orders_small[dollar_amount])`

`distinct_months = DISTINCTCOUNT(orders_small[month])`

`monthly_average = DIVIDE([sum_amount], [distinct_months])`

- Line graphs and tables by product over time

- Dollar amount
- Number of distinct customers
- Number of orders

- Filter by product and month

- Fill in zeros when product was available

- Show missing items

- Doesn’t work in this example

`sum_amount_zero = SUM(orders_small[dollar_amount]) + 0`

- Doesn’t fix table

`monthly_average = DIVIDE([sum_amount], 12)`

- Doesn’t fix graph

- Clean up the data by:

- Importing monthly data

- Importing zero

- Tend to work easily

- But tend to not be extendable

- Line graphs and tables by product over time

- Dollar amount
- Number of distinct customers
- Number of orders

- Filter by product and month

- Fill in zeros when product was available

- Calendar Table Setup

- Table created in Power BI that contains all the dates you need and associated information, using the CALENDAR function.

- The CALENDAR function needs a start date and end date.

- Fills in the day in between (including start and end dates).

- Three examples

```
cal_table = CALENDAR(
DATE(2020, 01, 01),
DATE(2020, 12, 31))
```

```
cal_table = CALENDAR(
DATE(YEAR(TODAY()) - 1, 01, 01),
DATE(YEAR(TODAY()) - 1, 12, 31))
```

```
cal_table = CALENDAR(
MIN(orders_big[date]),
MAX(orders_big[date]))
```

`cal_month = STARTOFMONTH(cal_table[DATE])`

- Calendar Table Setup +

- Star Schema

- Main orders_big table in the middle

- Important dimensions on the outside

`product = SUMMARIZECOLUMNS(orders_big[product])`

`customer = SUMMARIZECOLUMNS(orders_big[customer_id])`

- Calendar Table Setup +

- Star Schema +

- ADDMISSINGITEMS()

- Adds rows with empty values to a table returned by SUMMARIZECOLUMNS. (MS Docs)

- Add the rows with empty measure values back. (dax.guide)

- ShowAll_ColumnName - What you want to fill in

- Table - SUMMARIZECOLUMNS table with missing data (based on orders_big table here)

- GroupBy_ColumnName - What you want to fill in (same as ShowAll_ColumnName unless ROLLUP)

- FilterTable - Filter out unneeded combinations

- ShowAll_ColumnName -

`cal_table[cal_month], 'product'[product]`

- Table -

```
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])
```

```
orders_zeros_added =
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])),
cal_table[cal_month], 'product'[product])
```

```
orders_zeros_added =
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])),
cal_table[cal_month], 'product'[product]),
"amount filled", IF(ISBLANK([amount]), 0, [amount])))
```

- Might need to turn this off

- Filter for product availability

```
product_availability =
FILTER(CROSSJOIN(cal_table, product),
cal_month[month] >= product_dates[start_month] &&
cal_month[month] <= product_dates[end_month])
```

- No filter

```
orders_zeros_added =
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])),
cal_table[cal_month], 'product'[product])
```

- Filter

```
orders_zeros_added =
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])),
cal_table[cal_month], 'product'[product], product_availability)
```

```
orders_zeros_added = CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount])),
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount])))
```

- What if you want the graph to be continuous or want all the months with missing for unavailable?

- Let’s backtrack a little bit…

`product_zeros = SUMMARIZECOLUMNS(orders_big[product])`

```
cal_table_month =
var CalendarDates =
GENERATE( CALENDAR(DATE(2020, 01, 01),
DATE(2020, 12, 31)),
ROW("month", DATE(YEAR( [Date] ), MONTH( [Date] ), 01)))
return
SUMMARIZE(CalendarDates, [month])
```

```
product_availability = FILTER(
CROSSJOIN(cal_table_month, product_dates),
cal_table_month[month] >= product_dates[start_month] &&
cal_table_month[month] <= product_dates[end_month])
```

```
available = LOOKUPVALUE(product_availability[product],
product_availability[month], orders_zeros_added[cal_month],
product_availability[product], orders_zeros_added[product])
```

```
amount_filled_missing = IF(ISBLANK(orders_zeros_added[available]),
BLANK(), orders_zeros_added[amount filled])
```

- BLANK is not like SQL NULL

- Converts to 0 in sums and subtractions
- Stays BLANK in divisions and multiplication

1 - (A/B) = (B/B) - (A/B) = (B - A)/B

- If B is BLANK
- Left side = 1
- Right side = BLANK

- Filter for product availability

- Add Customers and Orders measures

```
orders_zeros_added =
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount]),
"customers", DISTINCTCOUNT(orders_big[customer_id])),
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount]),
"customers filled", IF(ISBLANK([customers]), 0, [customers])))
```

```
orders_zeros_added =
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product],
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product],
"amount", SUM(orders_big[dollar_amount]),
"customers", DISTINCTCOUNT(orders_big[customer_id]),
"orders", DISTINCTCOUNT(orders_big[order_number])),
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount]),
"customers filled", IF(ISBLANK([customers]), 0, [customers]),
"orders filled", IF(ISBLANK([orders]), 0, [orders])))
```

- Might need to watch DISTINCTCOUNT if needed in other aggregations.

- Bring all customers through in the SUMMARIZECOLUMNS, but not in the ShowAll_ColumnName options.

- This will fill in missing product-month pairs but not in combination with customers/order.
- Get rows with no customer_id or order_id.

- Filter for product availability

- Add Customers and Orders measures

- Filters

- Just use the built in filters for the table

- Use Star Schema again (will need to create new tables)

- That’s pretty much it.