Category: data Modeling With Dax

Budget Measures and Analysis ”; Previous Next Budgeting involves estimating the cash flows of a company over a financial year. The financial position of the company, its goals, expected revenues, and expenses are taken into account in budgeting. However, the market conditions may change during the financial year and the company may have to reset its goals. This requires analyzing the financial data with the budget estimated at the beginning of the financial year (Budget Sum) and the actual expended sum from the beginning of the financial year to date (YTD Actual Sum). At any time during a financial year, you can calculate the following − Unexpended Balance Unexpended Balance is the budget remaining after the actual expenses, i.e. Unexpended Balance = YTD Budget Sum – YTD Actual Sum Budget Attainment % Budget Attainment % is the percentage of the budget that you have spent to date, i.e. Budget Attainment % = YTD Actual Sum/YTD Budget Sum These calculations help those companies that use budgeting to make decisions. Creating Unexpended Balance Measure You can create Unexpended Balance measure as follows − Unexpended Balance:=CALCULATE( [YTD Budget Sum],ALL(”Finance Data”[Date]) )-[YTD Actual Sum] Creating Budget Attainment Percentage Measure You can create Budget Attainment Percentage measure as follows − Budget Attainment %:=IF([YTD Budget Sum],[YTD Actual Sum]/CALCULATE([YTD Budget Sum],ALL(”Finance Data”[Date])),BLANK()) Analyzing Data with Budget Measures Create a Power PivotTable as follows − Add Month from the Date table to Rows. Add the measures Budget Sum, YTD Budget Sum, YTD Actual Sum, Budget Attainment % and Unexpended Balance from Finance Data table to Values. Insert a Slicer on the Fiscal Year field. Select FY2016 in the Slicer. Print Page Previous Next Advertisements ”;

Understanding Data Tables ”; Previous Next Data Analysis involves browsing data over time and making calculations across time periods. For example, you might have to compare the current year’s profits with the previous year’s profits. Similarly, you might have to forecast the growth and profits in the coming years. For these, you need to use grouping and aggregations over a period of time. DAX provides several Time Intelligence functions that help you perform most of such calculations. However, these DAX functions require a Date table for usage with the other tables in the data model. You can either import a Date table along with other data from a data source or you can create a Date table by yourself in the data model. In this chapter, you will understand different aspects of Date tables. If you are conversant with Date tables in the Power Pivot data model, you can skip this chapter and proceed with the subsequent chapters. Otherwise, you can understand the Date tables in the Power Pivot data model. What is a Date Table? A Date Table is a table in a data model, with at least one column of contiguous dates across a required duration. It can have additional columns representing different time periods. However, what is necessary is the column of contiguous dates, as required by the DAX Time Intelligence functions. For example, A Date table can have columns such as Date, Fiscal Month, Fiscal Quarter, and Fiscal Year. A Date table can have columns such as Date, Month, Quarter, and Year. Date Table with Contiguous Dates Suppose you are required to make calculations in the range of a calendar year. Then, the Date table must have at least one column with a contiguous set of dates, including all the dates in that specific calendar year. For example, suppose the data you want to browse has dates from April 1st, 2014 through November 30th, 2016. If you have to report on a calendar year, you need a Date table with a column – Date, which contains all the dates from January 1st, 2014 to December 31st, 2016 in a sequence. If you have to report on a fiscal year, and your fiscal year end is 30th June, you need a Date table with a column – Date, which contains all the dates from July 1st, 2013 to June 30th, 2017 in a sequence. If you have to report on both calendar and fiscal years, then you can have a single Date table spanning the required range of dates. Your Date table must contain all of the days for the range of every year in the given duration. Thus, you will get contiguous dates within that period of time. If you regularly refresh your data with new data, you will have the end date extended by a year or two, so that you do not have to update your Date table often. A Date table looks like the following screenshot. Adding a Date Table to the Data Model You can add a Date table to the data model in any of the following ways − Importing from a relational database, or any other data source. Creating a Date table in Excel and then copying or linking to a new table in Power Pivot. Importing from Microsoft Azure Marketplace. Creating a Date Table in Excel and Copying to the Data Model Creating a Date table in Excel and copying to the data model is the easiest and most flexible way of creating a Data table in the data model. Open a new worksheet in Excel. Type – Date in the first row of a column. Type the first date in the date range that you want to create in the second row in the same column. Select the cell, click the fill handle and drag it down to create a column of contiguous dates in the required date range. For example, type 1/1/2014, click the fill handle and drag down to fill the contiguous dates up to 31/12/2016. Click the Date column. Click the INSERT tab on the Ribbon. Click Table. Verify the table range. Click OK. The table of a single column of dates is ready in Excel. Select the table. Click Copy on the Ribbon. Click the Power Pivot window. Click Paste on the Ribbon. This will add the contents of the clipboard to a new table in the data model. Hence, you can use the same method to create a Date table in an existing data model also. Paste preview dialog box appears as shown in the following screenshot. Type Date in the Table Name box. Preview the data. Check the box – Use first row as column headers. Click OK. This copies the contents of the clipboard to a new table in the data model. Now, you have a Date table in the data model with a single column of contiguous dates. The header of the column is Date as you had given in the Excel table. Adding New Date Columns to the Date Table Next, you can add calculated columns to the Date table as per the requirement for your calculations. For example, you can add columns – Day, Month, Year, and Quarter as follows − Day =DAY(”Date”[Date]) Month =MONTH(”Date”[Date]) Year =YEAR(”Date”[Date]) Quarter =CONCATENATE (“QTR “, INT ((”Date”[Month]+2)/3)) The resulting Date table in the data model looks like the following screenshot. Thus, you can add any number of calculated columns to the Date table. What is important and is required is that the Date table must have a column of contiguous dates that spans the duration of time over which you perform calculations. Creating a Date Table for a Calendar Year A calendar year typically includes the dates from 1st January to 31st December of a year and also includes the holidays marked for that particular year. When you perform calculations, you might have to take into account only the working days, excluding weekends and holidays. Suppose, you want

Data Modeling with DAX – Overview ”; Previous Next The decision makers in all the organizations have identified the need for analyzing the historical data of their organization in specific, and of the industry in general. This is becoming crucial day-by-day in the present competitive world, to meet the ever-changing business challenges. Big Data and Business Intelligence have become the buzzwords in the business world. Data sources have become huge and data formats have become variant. The need of the hour is to have simple-to-use tools to handle the ever-flowing vast data in less time to gain insight and make relevant decisions at the appropriate time. Data analysts can no longer wait for the required data to be processed by the IT department. They require a handy tool that enables them to quickly comprehend the required data and make it available in a format that helps the decision makers take required action at the right time. Microsoft Excel has a powerful tool called as Power Pivot that was available as an add-in in the prior versions of Excel and is built-in feature in Excel 2016. The database of Power Pivot, called the data model and the formula language that works on the data model, called DAX (Data Analysis Expressions) enables an Excel user to perform tasks such as data modeling and analysis in no time. In this tutorial, you will learn data modeling and analysis using DAX, based on the Power Pivot data model. A sample Profit and Analysis database is used for the illustrations throughout this tutorial. Data Modeling and Analysis Concepts The data that you obtain from different variety of sources, termed as raw data, needs processing before you can utilize it for analysis purposes. You will learn about these in the chapter − Data Modeling and Analysis Concepts. Data Modeling and Analysis with Excel Power Pivot Since the tool that you will be mastering in this tutorial is Excel Power Pivot, you need to know how the data modeling and analysis steps are carried out in Power Pivot. You will learn these at a broader level in the chapter – Data Modeling and Analysis with Excel Power Pivot. As you proceed with the subsequent chapters, you will learn about the different facets of Power Pivot, DAX and DAX functions in data modeling and analysis. By the end of the tutorial, you will be able to perform data modeling and analysis with DAX for any context at hand. Print Page Previous Next Advertisements ”;

YoY Finance Measures and Analysis ”; Previous Next Year-over-Year (YoY) is a measure of growth. It is obtained by subtracting the actual sum of the previous year from the actual sum. If the result is positive, it reflects an increase in actual, and if it is negative, it reflects a decrease in actual, i.e. if we calculate year-over-year as − year-over-year = (actual sum –prior year actual sum) If the actual sum > the prior year actual sum, year-over-year will be positive. If the actual sum < the prior year actual sum, year-over-year will be negative. In the financial data, accounts such as the expense accounts will have debit (positive) amounts and the revenue accounts will have credit (negative) amounts. Hence, for the expense accounts, the above formula works fine. However, for the revenue accounts, it should be the reverse, i.e. If the actual sum > the prior year actual sum, year-over-year should be negative. If the actual sum < the prior year actual sum, year-over-year should be positive. Hence for the revenue accounts, you have to calculate year-over-year as − year-over-year = -(actual sum – prior year actual sum) Creating Year-over-Year Measure You can create Year-over-Year measure with the following DAX formula − YoY:=IF(CONTAINS(Accounts, Accounts[Class],”Net Revenue”),-([Actual Sum]-[Prior Year Actual Sum]), [Actual Sum]-[Prior Year Actual Sum]) In the above DAX formula − DAX CONTAINS function returns TRUE, if a row has “Net Revenue” in the column Class in the Accounts table. DAX IF function then returns –([Actual Sum]-[ Prior Year Actual Sum]). Otherwise, DAX IF function returns [Actual Sum]-[ Prior Year Actual Sum]. Creating Year-over-Year Percentage Measure You can represent Year-over-Year as a percentage with the ratio − (YoY) / (Prior Year Actual Sum) You can create the Year-over-Year Percentage measure with the following DAX formula − YoY %:=IF([Prior Year Actual Sum], [YoY] / ABS([Prior Year Actual Sum]),BLANK()) DAX IF function is used in the above formula to ensure that there is no division by zero. Analyzing Data with Year-over-Year Measures Create a Power PivotTable as follows − Add the fields Class and Sub Class from the Accounts table to Rows. Add the measures – Actual Sum, Prior Year Actual Sum, YoY and YoY % to Values. Insert a Slicer on the field Fiscal Year from the Date table. Select FY2016 in the Slicer. Creating Budget Year-over-Year Measure You can create Budget Year-over-Year measure as follows − Budget YoY: = IF(CONTAINS(Accounts,Accounts[Class],”Net Revenue”), – ([Budget Sum] – [Prior Year Actual Sum]), [Budget Sum] – [Prior Year Actual Sum]) Creating Budget Year-over-Year Percentage Measure You can create Budget Year-over-Year Percentage measure as follows − Budget YoY %:=IF([Prior Year Actual Sum] , [Budget YoY] / ABS ([Prior Year Actual Sum]) , BLANK()) Analyzing Data with Budget Year-over-Year Measures Create a Power PivotTable as follows − Add the fields Class and Sub Class from the Accounts table to Rows. Add the measures – Budget Sum, Prior Year Actual Sum, Budget YoY and Budget YoY % to Values. Insert a Slicer on the field Fiscal Year from the Date table. Select FY2016 in the Slicer. Creating Forecast Year-over-Year Measure You can create Forecast Year-over-Year measure as follows − Forecast YoY:=IF(CONTAINS(Accounts,Accounts[Class],”Net Revenue”), – ([Forecast Sum] – [Prior Year Actual Sum]), [Forecast Sum] – [Prior Year Actual Sum]) Creating Forecast Year-over-Year Percentage Measure You can create Forecast Year-over-Year Percentage measure as follows − Forecast YoY %:=IF([Prior Year Actual Sum],[Forecast YoY]/ABS([Prior Year Actual Sum]),BLANK()) Analyzing Data with Forecast Year-over-Year Measures Create a Power PivotTable as follows − Add the fields Class and Sub Class from the Accounts table to Rows. Add the measures – Forecast Sum, Prior Year Actual Sum, Forecast YoY and Forecast YoY % to Values. Insert a Slicer on the field Fiscal Year from the Data table. Select FY2016 in the Slicer. Print Page Previous Next Advertisements ”;