###### Project

# Demystifying DAX: Projected Sales Analysis and Computation

This comprehensive project delves into the details of implementing a calculated formula for projecting sales using DAX (Data Analysis Expressions). Gain a deep understanding of each fragment of the formula and how they collaborate to provide insightful projections.

## Demystifying DAX: Projected Sales Analysis and Computation

##### Description

As part of the project, you will engage in studying a key DAX formula focused on sales projections. The formula calculates average sales per day, scrutinizes the remaining days in the year, then multiplies the two to get the projected sales. If there are no remaining days left in the parsed year, the formula takes the damage-control route and returns a blank value instead of a misleading zero. Throughout this project, you will not only decipher the working of this formula but also explore other crucial DAX functions and concepts. This provides you with a comprehensive understanding of the language.

# Understanding DAX and Its Applications

Data Analysis Expressions (DAX) is a formula language used extensively in Power BI, Analysis Services, and Power Pivot in Excel to conduct data modeling, data analysis, and reporting. The objective of this markdown is to implement a calculated formula for projecting sales using DAX.

## Prerequisites

### Required Software:

- Power BI Desktop: DAX is primarily used within the context of Microsoft Power BI, so it's necessary to have this software installed. You can install Power BI Desktop from
Here .

### Dataset:

- A dataset is needed for the DAX execution. Make sure you have a dataset that includes sales data. For simplicity, we'll assume a dataset with at least these two fields: 'OrderDate' and 'Sales'. 'OrderDate' is your date field and 'Sales' indicates the amount of sales for this date.

## Setup and Load Data

After installing and launching Power BI Desktop, the first step is to load the data into Power BI.

- Click on "Get Data".
- Select "Excel" (or your desired data source).
- Browse to your dataset and click "Open".
- In the Navigator window, select the table you want to import and click "Load".

## Creating Calculated Columns and Measures Using DAX

Now we visit the DAX implementation. DAX formulas consist of functions, operators, and constants that can be used to define custom formulas in Power BI, Analysis Services, and Power Pivot in Excel.

### Total Sales

To use DAX to calculate the total sales, you can create a new measure. In Power BI Desktop, go to the "Modeling" tab and click on the "New Measure" button.

Enter the following DAX formula and press enter:

`Total Sales = SUM('Table'[Sales])`

This will summarize the total of the 'Sales' column which corresponds to the total sales.

### Sales Projection

Let's say, for instance, we want to project our future sales based on a growth percentage. We can create another measure, `Sales Projection`

, that calculates the projected sales as an increase over the total sales. Suppose the growth percentage is 5%.

`Sales Projection = [Total Sales] * 1.05`

This formula implies that the projected sales are 105% of the current total sales, which corresponds to an increase of 5%.

## Conclusion

These are basic examples of creating measures using DAX that can calculate columns or make projections. The essence of DAX is its flexibility and power, allowing any complex analysis to be done if you understand the basics well. There are a variety of other DAX functions such as COUNT, AVERAGE, MIN, MAX, etc. as well as more advanced functions such as CALCULATE, FILTER, SAMEPERIODLASTYEAR which you can explore as per your requirement.

# Sales Projections – Why & What?

Sales projections are essential for strategic planning, budgeting, and managing cash flows in businesses. Accurate sales predictions allow companies to make informed decisions, from daily operations to strategic business decisions. DAX (Data Analysis Expressions) is a library of functions and operators that can be combined to create formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel. These expressions assess the usability and performance of data models.

Given that you've already have solutions for understanding DAX and its applications, we'll now cover implementing a sales projection formula using DAX. Here's a feasible implementation and application of a projected sales calculation.

## Sales Projections Implementation

To project future sales, we first need historical sales data. For the sake of simplicity, let's use the following data model which includes a Sales table with Sales Amount and a Date table:

```
Sales =
DATATABLE (
"Date", DATETIME,
"Sales Amount", CURRENCY,
{
{ "01/01/2019", 100 },
{ "01/02/2019", 150 },
{ "01/03/2019", 125 },
...
{ "12/31/2019", 175 }
}
)
```

We'll be using the LINEST function to calculate the linear equation that best approximates past sales data as our primary path for future sales predictions.

First, we calculate the slope of the sales line. This will indicate the trend in sales, which will be used to forecast future sales. Here's how we can do this:

```
Sales Slope =
VAR SalesTable = SUMMARIZE( 'Sales', 'Date'[Date], "SalesAmount", SUM('Sales'[Sales Amount]) )
VAR SalesRegression = LINEST( SalesTable, [SalesAmount], 'Date'[Date] )
RETURN SalesRegression[1]
```

Next, we calculate the intercept, which is the expected value of sales when the date is zero.

```
Sales Intercept =
VAR SalesTable = SUMMARIZE( 'Sales', 'Date'[Date], "SalesAmount", SUM('Sales'[Sales Amount]) )
VAR SalesRegression = LINEST( SalesTable, [SalesAmount], 'Date'[Date] )
RETURN SalesRegression[2]
```

Now, having the slope and intercept, we can forecast future sales:

`Projected Sales = [Sales Slope] * MAX('Date'[Date]) + [Sales Intercept]`

This formula relies on the linear regression of past sales data to predict future sales. Note that we're assuming linearity in the sales trend, which might not always be the case in the real world.

Please remember that you must adapt this implementation to your data model, but the concepts and methodology will remain the same. Consider also that you might need to adjust the formula depending on how your date is formatted. Please note that more often than not, real-life data will require more complex models, which may include seasonality, additional variables, etc. However, this model provides a solid base from which to start building more complex sales projection models.

**Caution**: This is a simplistic projection model and does not cater for factors such as seasonality, market fluctuations among others. It is simply a straight line approximation based on historical sales data, and the future may not necessarily follow the same trend. However, it serves as a good starting point, and more sophisticated models can be built upon this basic model.

# Decoding the DAX Formula – Overview

Assuming you have a sales data model containing `Sales`

(amount sold), `Date`

(date of sale) and `Product`

(product sold). Our goal is to create a DAX formula projecting future sales.

## DAX Formula

Here is a possible formula in DAX for sales projection, based on average of previous sales:

```
Projected_Sales =
CALCULATE(
AVERAGE('Sales'[AmountSold]),
FILTER(
ALL('Sales'[Date]),
'Sales'[Date] <= MAX('Sales'[Date])
)
) + CALCULATE(
AVERAGE('Sales'[AmountSold]),
FILTER(
ALL('Sales'[Date]),
'Sales'[Date] > MAX('Sales'[Date])
)
)
```

### Decoding the Formula

Let's break down this formula to understand its components.

#### CALCULATE Function

CALCULATE is one of the most powerful and frequently used functions in DAX. It evaluates the expression given as the first argument in the context that is potentially modified by the further (2nd, 3rd, …) arguments.

`CALCULATE(`, , , …)

In our formula, the first `CALCULATE`

calculates average of `AmountSold`

for dates less than or equal to the maximum date in the sales data. The second `CALCULATE`

calculates the average `AmountSold`

for dates greater than the maximum date.

#### AVERAGE Function

AVERAGE function returns the arithmetic mean of values in a column.

`AVERAGE(`)

In the formula, `AVERAGE('Sales'[AmountSold])`

calculates the average sales amount.

#### FILTER Function

FILTER is an iterative function that creates a table derived from an already existing one but that only contains the rows that meet a condition specified in the second argument.

`FILTER(, `)`FILTER`

is used twice in our formula. The first `FILTER`

condition `'Sales'[Date] <= MAX('Sales'[Date])`

gets past sales data up to the present. The second `FILTER`

condition `'Sales'[Date] > MAX('Sales'[Date])`

gets future sales data, excluding those we already have.

Overall, this DAX formula calculates and consolidates sales projections by adding the average of past sales to the average of future ones. The formula can be applied within Power BI to analyze and visualize the sales projection results. It can also be fine-tuned or extended according to the specific requirements and complexity of your sales data.

# Dissecting the Formula: Calculating Total Sales YTD

As you have already understood the basics of DAX (Data Analysis Expressions) and its applications, as well as the overview of the DAX formula, it's time to examine some specific applications, specifically how to calculate total sales Year-to-Date (YTD).

In general, calculating Year-to-Date sales means calculating the total sum of sales from the beginning of the year until the current date. DAX provides a number of useful functions to make this calculation easy to execute.

### Practical Implementation:

The procedure for calculating Year-to-Date sales is stated below along with the associated DAX formula.

**Creating a Measure** :

In DAX, we define our calculations in Measures. Therefore, create a new measure in your sales table. You can name it `Total Sales`

.

`Total Sales = SUM ('Sales'[Sales Amount])`

This measure will calculate the total of the 'Sales Amount' column in the 'Sales' table.

**Calculating Total Sales YTD** :

To calculate total sales Year-to-Date, we can use the `TOTALYTD()`

function in DAX. This function will calculate the sum of sales from the start of the year until the current date.

The `TOTALYTD()`

function requires two parameters: the first one is the expression that we want to compute (in this case, our measure `Total Sales`

), and the second one is the `Dates`

column that will be used for determining the periods.

Here is how you perform the calculation:

`Total Sales YTD = TOTALYTD ([Total Sales], 'Date'[Date])`

In the code snippet above, `[Total Sales]`

corresponds to the measure we created before, and `'Date'[Date]`

is the date column in your `Date`

table.

Please ensure that the `Date`

table is linked with the `Sales`

table on the 'Date' column. The date range in the `Date`

table should cover the sales date range in the `Sales`

table.

With this formula, you will get total sales data for the year to the current day.

Remember, DAX assumes that the year starts on January 1 and ends on December 31. If your financial year is different, you will need to adjust the formula accordingly.

### Conclusion

This guide shows how to perform a YTD calculation on sales data in DAX. It's a simple matter of creating a measure and then using the `TOTALYTD()`

function. Calculations like these, along with many others, make DAX a powerful tool for analyzing business data. Be sure to experiment further with the different capabilities that DAX offers.

# Dissecting the Formula: Counting Days YTD

In this section, our target is to develop the DAX measure for counting the number of Year-to-Date (YTD) days. This mathematical figure will play a significant role in projecting sales for the current year.

Since we are focusing on the specific computation of counting YTD days, it is assumed that you have a normal date table created in your data model.

Also, it's worth mentioning, DAX does provide a function `TOTALYTD`

which will help us accomplish our task. Remember, in the context of this exercise, the current date is assumed to be TODAY's date.

`Current Year = YEAR(TODAY())`

```
YTD Days Count Calculation =
CALCULATE(
COUNTROWS('Date'),
FILTER(
ALL('Date'),
'Date'[Year] = [Current Year] && 'Date'[Date]<=TODAY()
)
)
```

## Explanation

The `YTD Days Count Calculation`

measure accomplishes the task of counting days until today in the current year through following steps:

`COUNTROWS('Date')`

: will count all the dates present in your date table.
- However, we don't need to count all dates. We want to distinguish our count to only this year's dates until the current date. That's where
`FILTER`

function comes into play.
`FILTER(ALL('Date'), 'Date'[Year] = [Current Year] && 'Date'[Date]<=TODAY())`

: FILTER and ALL functions combined provides a recalculated table where every row satisfies the provided conditions. In our case, the conditions are:
- Date year must be same as current year (
`'Date'[Year] = [Current Year]`

).
- Date must be less than or equal to today's date (
`'Date'[Date]<=TODAY()`

).

Thus, the developed DAX measure counts all the days of the current year until today's date i.e., if today's date is June 1, then the measure will return the number of days from January 1 to June 1.

Remember, this implementation assumes you have a tabular data model established with Power BI and a properly structured date table in place. The formula provided should be incorporated into a new measure within this model to provide the YTD day count required for sales projection modeling.

# Getting to Know the Filter Function in DAX

In DAX (Data Analysis Expressions), the FILTER function is an essential tool that allows us to manipulate and evaluate data in a number of useful ways. The power of FILTER lies in its ability to create a subset of data based on the criteria specified.

The syntax for the FILTER function is as follows:

`FILTER (`

`, `)

Here '

' refers to the table on which we want to apply the criteria, and '' is the filter condition that returns TRUE/FALSE.
In our sales projections scenario, let's say we want to project the sales for a specific product category. For this use case, we will use the FILTER function to create a subset consisting of only that product category.

## Implementing FILTER in Sales Projections

Suppose we have a `Sales`

table which contains columns `ProductName`

, `ProductCategory`

, and `Revenue`

among others.

If we need to calculate the total sales for a particular product category "Electronics", we'd use the FILTER function in the following way:

```
ELECTRONICS_SALES =
CALCULATE (
SUM (Sales[Revenue]),
FILTER (
Sales,
Sales[ProductCategory] = "Electronics"
)
)
```

In this example, the FILTER function works to return only the rows in the `Sales`

table where the `ProductCategory`

is "Electronics". It then sums the `Revenue`

for these rows. This is then output as the projected sales for "Electronics" category.

## Implementing FILTER with Multiple Conditions

The FILTER function can also be used with multiple filter conditions. If we want to include another condition, for example, to only include sales from 2020, we can do so by using the `&&`

operator.

```
ELECTRONICS_SALES_2020 =
CALCULATE (
SUM (Sales[Revenue]),
FILTER (
Sales,
Sales[ProductCategory] = "Electronics" && YEAR(Sales[OrderDate]) = 2020
)
)
```

In this snippet, the additional condition is `YEAR(Sales[OrderDate]) = 2020`

. It limits the rows in the `Sales`

table to include only those where the `OrderDate`

is from the year 2020.

These examples illustrate a simple and practical implementation of the FILTER function in DAX, specifically catered towards sales projections. The FILTER function can be quite powerful and versatile in dealing with data manipulation and evaluation based on specific criteria.

Just note that performance could be affected when filtering large datasets, so always test and review your DAX measures for optimization.

# Running the DAX Calculation and Understanding the Output

## Implementing the DAX Calculation for Projected Sales

Given the earlier workings, we already have:

- Total Sales YTD
- Count of Days YTD

We'll now proceed to build on that by creating a calculated measure for the projected sales. For projecting total sales, we'll have to extend the Total Sales YTD formula proportionally to the total number of days in the year.

Importantly, the number of total days in a year fluctuates between 365 and 366, depending on if it's a leap year or not.

Create a new measure and input the following DAX formula:

```
Total Days In Year =
IF (
ISLEAPYEAR(MAX(Sales[OrderDate])),
366,
365
)
```

The 'IF' function in DAX checks whether the condition, used as the first argument, is met. It then returns one value (second argument) if the condition is TRUE and another value (third argument) if the condition is FALSE. The 'ISLEAPYEAR' function checks if the year of the provided date, 'OrderDate', is a leap year or not.

Now, let's calculate the Projected Sales using the above measures. Here is the DAX formula that we'd use:

```
Projected Sales =
(SUM(Sales[Total Sales YTD]) / SUM(Sales[Count of Days YTD])) * [Total Days In Year]
```

The formula is taking the total sales for the year to date, dividing it by the count of days thus far in the year, and then multiplying the whole result by the total number of days in the year. This gives an extrapolated measure of what the total sales would be for the whole year if sales continue at the same rate.

## Understanding the Output

After running these calculations, the returned result will be your projected total sales for the entire year, based on the sales data you have so far in the year. This projection assumes that the rate of sales will remain constant over the year.

Consider the output as a dynamic metric that updates with the introduction of new data. As we progress further through the year, and the sales data for more days are included, the measure for projected sales should become progressively more accurate.

If you pair this measure with a visualization, for instance, a line graph that tracks actual sales and projected sales over time, you could watch as new sales data comes in and see whether your projections are tracking closely with your actual sales.

This holistic approach gives you a clear and continuously updated projection of your total upcoming sales and provides an easy and intuitive way for exploring this data, which is one of the primary strengths of DAX and its integration with Power BI.

# Advanced DAX Functions and Concepts

## Projecting Sales Using DAX Calculations

In this part, we're going to use advanced DAX calculations to create a more fine-tuned approach to our sales projections. Our primary goal is to project future sales based on historical data. We'll construct a forecast using a few different DAX functions, mainly `CALCULATE`

, `EARLIER`

, and `AVERAGEX`

.

Assumptions:

- We have a table named 'Sales' with columns 'Date', 'SalesAmount'.
- 'Calendar' table is available with 'Date', 'Year', 'MonthNumber'.

```
Sales =
SUMMARIZE(
ADDCOLUMNS(
'Sales',
"DateYear",(YEAR('Sales'[Date])),
"DateMonth",(MONTH('Sales'[Date])),
"SalesAmount",'Sales'[SalesAmount]
),
[DateYear],[DateMonth], "MonthlySales", SUM('Sales'[SalesAmount])
)
```

## Constructing the Sales Forecast

We generate the sales forecast by calculating a moving average based on sales in the previous months.

```
SalesForecast =
CALCULATE(
AVERAGEX(
FILTER(
ALL(Sales),
AND(
Sales[DateYear] = EARLIER(Sales[DateYear]),
Sales[DateMonth] <= EARLIER(Sales[DateMonth])
)
),
Sales[MonthlySales]
)
)
```

- The
`FILTER`

function is used to generate a table that contains rows for the current and previous months within the same year.
- We use
`EARLIER`

function as it returns the value of the earlier row context. This is useful when you're inside a row context and want to relate the value to a previous row context for calculations.
- The
`AVERAGEX`

function computes the average of the 'MonthlySales' column values for each row in the filtered table returned by FILTER.
- The
`CALCULATE`

function changes the context in which the `AVERAGEX`

function is evaluated, by applying the filter context established by the 'Sales' table

## Measure for Cumulative Sales Forecast

This measure calculates the running total of the 'SalesForecast' measure. A separate 'Calendar' table has been considered for 'Year', 'MonthNumber' columns.

```
Cumulative Sales Forecast =
CALCULATE(
[SalesForecast],
FILTER(ALL(Calendar),
Calendar[Year] = MAX(Calendar[Year]) && Calendar[MonthNumber] <= MAX(Calendar[MonthNumber] )
)
```

- We use
`ALL`

function which removes the applied filters from the tables or columns supplied and brings back all the rows in the 'Calendar' table.
- The
`MAX`

function visualizes each month up to the maximum current month.
- The
`CALCULATE`

function modifies the filter context to include only rows that have the same year as the maximum current year and a month less than or equal to the current month in the 'Calendar' table.

Now, we have measures for actual sales, sales forecast, and cumulative sales forecast and these can be included in a visual report for comparison and analysis. This advanced level DAX application greatly aids in predicting sales for future periods.

Remember to always check and validate your DAX calculations with your existing data, to ensure they’re working as expected. It’s a good practice to understand the data and validation rules before making projections based on them.

##### More Code Generators

Apache Flink Code Generator Apache Pig Code Generator Azure Data Factory Code Generator C/C++ Code Generator CouchDB Code Generator DAX Code Generator Excel Code Generator Firebase Code Generator Google BigQuery Code Generator Google Sheets Code Generator GraphQL Code Generator Hive Code Generator Java Code Generator JavaScript Code Generator Julia Code Generator Lua Code Generator M (Power Query) Code Generator MATLAB Code Generator MongoDB Code Generator Oracle Code Generator PostgreSQL Code Generator Power BI Code Generator Python Code Generator R Code Generator Redis Code Generator Regex Code Generator Ruby Code Generator SAS Code Generator Scala Code Generator Shell Code Generator SPSS Code Generator SQL Code Generator SQLite Code Generator Stata Code Generator Tableau Code Generator VBA Code Generator