Category: data Modeling With Dax

YoY Headcount Measures and Analysis ”; Previous Next In the previous chapter, you have learnt how to create base Headcount measures – i.e. Actual Total Headcount, Budget Total Headcount, and Forecast Total Headcount. In this chapter, you will learn how to create Year-Over-Year Headcount measures and how you can analyze the data with these measures. Creating Year-over-Year Actual Ending Headcount Measure You can create Year-over-Year Actual Ending Headcount Measure as follows − YoY Actual Ending Headcount:=[Actual Ending Head Count]-[Prior Year Actual Ending Headcount] Creating Year-over-Year Actual Average Headcount Measure You can create Year-over-Year Actual Average Headcount Measure as follows − YoY Actual Average Headcount:= [Actual Average Headcount]-[Prior Year Actual Average Headcount] Creating Year-over-Year Actual Total Headcount Measure You can create Year-over-Year Actual Total Headcount Measure as follows − YoY Actual Total Headcount:=[Actual Total Head Count]-[Prior Year Actual Total Headcount] Analyzing Data with Year-over-Year Actual Headcount Measures Create a Power PivotTable as follows − Add the fields Fiscal Quarter and Month from the Date table to Rows. Add the measures – Actual Ending Head Count, Prior Year Actual Ending Head Count, YoY Actual Ending Head Count to Values. Insert a Slicer on the field Fiscal Year. Select FY2016 in the Slicer. Create another Power PivotTable on the same worksheet as follows − Add the fields Fiscal Quarter and Month from the Date table to Rows. Add the measures – Actual Average Head Count, Prior Year Actual Average Head Count, YoY Actual Average Head Count to Values. Connect the Slicer to this PivotTable as follows − Click the Slicer. Click the Options tab under Slicer Tools on the Ribbon. Click Report Connections. Report Connections dialog box appears. Select the above two PivotTables. Click OK. Creating Year-over-Year Budget Ending Headcount Measure You can create Year-over-Year Budget Ending Headcount Measure as follows − YoY Budget Ending Headcount:= [Budget Ending Head Count]-[Prior Year Actual Ending Headcount] Creating Year-over-Year Budget Average Headcount Measure You can create Year-over-Year Budget Average Headcount Measure as follows − YoY Budget Average Headcount:= [Budget Average Headcount]-[Prior Year Actual Average Headcount] Creating Year-over-Year Budget Total Headcount Measure You can create Year-over-Year Budget Total Headcount Measure as follows − YoY Budget Total Headcount:=[Budget Total Headcount]-[Prior Year Actual Total Headcount] Creating Year-over-Year Forecast Ending Headcount Measure You can create Year-over-Year Forecast Ending Headcount Measure as follows − YoY Forecast Ending Headcount:= [Forecast Ending Head Count]-[Prior Year Actual Ending Headcount] Creating Year-over-Year Forecast Average Headcount Measure You can create Year-over-Year Forecast Average Headcount Measure as follows − YoY Forecast Average Headcount:= [Forecast Average Headcount]-[Prior Year Actual Average Headcount] Creating Year-over-Year Forecast Total Headcount Measure You can create Year-over-Year Forecast Total Headcount Measure as follows − YoY Forecast Total Headcount:=[Forecast Total Headcount]-[Prior Year Actual Total Headcount] Print Page Previous Next Advertisements ”;

Total Headcount Measures ”; Previous Next In the previous chapters, you have learnt how to create Count of Months measures and Average Headcount measures. You can use these measures to calculate the base Headcount Measures − Actual Total Headcount Budget Total Headcount Forecast Total Headcount In the subsequent chapters, you will learn how to use these base Headcount measures in other calculations such as YoY Headcount and Variance measures. Creating Actual Total Headcount Measure You can create Actual Total Headcount Measure as follows − Actual Total Head Count:= ”Finance Data”[Actual Average Headcount]*”Finance Data”[CountOfActualMonths] Creating Budget Total Headcount Measure You can create Budget Total Headcount Measure as follows − Budget Total Headcount:= ”Finance Data”[Budget Average Headcount]*”Finance Data”[CountOfBudgetMonths] Creating Forecast Total Headcount Measure You can create Forecast Total Headcount Measure as follows − Forecast Total Headcount:= ”Finance Data”[Forecast Average Headcount]*”Finance Data”[CountOfForecastMonths] Print Page Previous Next Advertisements ”;

Variance Measures and Analysis ”; Previous Next You can create variance measures such as variance to budget, variance to forecast, and forecast variance to budget. You can also analyze the finance data based on these measures. Creating Variance to Budget Sum Measure Create Variance to Budget Sum measure (VTB Sum) as follows − VTB Sum:=[Budget Sum]-[Actual Sum] Creating Variance to Budget Percentage Measure Create Variance to Budget Percentage measure (VTB %) as follows − VTB %:=IF([Budget Sum],[VTB Sum]/ABS([Budget Sum]),BLANK()) Analyzing Data with Variance to Budget Measures Create a Power PivotTable as follows − Add Fiscal Year from the Date table to Rows. Add the measures Actual Sum, Budget Sum, VTB Sum, VTB % from the Finance Data table to Values. Creating Variance to Forecast Sum Measure Create Variance to Forecast Sum (VTF Sum) measure as follows − VTF Sum:=[Forecast Sum]-[Actual Sum] Creating Variance to Forecast Percentage Measure Create Variance to Forecast Percentage measure (VTF %) as follows − VTF %:=IF([Forecast Sum],[VTF Sum]/ABS([Forecast Sum]),BLANK()) Analyzing Data with Variance to Forecast Measures Create a Power PivotTable as follows − Add Fiscal Year from the Date table to Rows. Add the measures Actual Sum, Forecast Sum, VTF Sum, VTF % from the Finance Data table to Values. Creating Forecast Variance to Budget Sum Measure Create Forecast Variance to Budget Sum (Forecast VTB Sum) measure as follows − Forecast VTB Sum:=[Budget Sum]-[Forecast Sum] Creating Forecast Variance to Budget Percentage Measure Create Forecast Variance to Budget Percentage (Forecast VTB Percentage) measure as follows − Forecast VTB %:=IF([Budget Sum],[Forecast VTB Sum]/ABS([Budget Sum]),BLANK()) Analyzing Data with Forecast Variance to Budget Measures Create a Power PivotTable as follows − Add Fiscal Year from the Date table to Rows. Add the measures Budget Sum, Forecast Sum, Forecast VTB Sum, Forecast VTB % from the Finance Data table to Values. Print Page Previous Next Advertisements ”;

Average Headcount Measures ”; Previous Next In the previous chapter, you have learnt how to calculate ending headcounts for a specific period. Likewise, you can create the average monthly headcount for any given selection of months. The Average Monthly Headcount is the sum of the monthly headcounts divided by the number of months in the selection. You can create these measures using DAX AVERAGEX function. Creating Actual Average Headcount Measure You can create Actual Average Headcount measure as follows − Actual Average Headcount:=AVERAGEX(VALUES(”Finance Data”[Fiscal Month]), [Actual Ending Head Count]) Creating Budget Average Headcount Measure You can create Actual Average Headcount measure as follows − Budget Average Headcount:=AVERAGEX(VALUES(”Finance Data”[Fiscal Month]), [Budget Ending Head Count]) Creating Forecast Average Headcount Measure You can create Forecast Average Headcount measure as follows − Forecast Average Headcount:=AVERAGEX( VALUES(”Finance Data”[Fiscal Month]), [Actual Ending Head Count]) Creating Prior Year Actual Average Headcount Measure You can create Prior Year Actual Average Headcount measure as follows − Prior Year Actual Average Headcount:=CALCULATE(”Finance Data”[Actual Average Headcount], DATEADD(”Date”[Date], -1, YEAR)) Analyzing Data with Average Headcount Measures Create a Power PivotTable as follows − Add the fields Fiscal Year and Month from the Date table to Rows. Add the measures Actual Average Headcount, Budget Average Headcount, Forecast Average Headcount, Prior Year Actual Average Headcount from Finance Data table to Values. Insert a Slicer on the Fiscal Year field. Select FY2016 in the Slicer. Print Page Previous Next Advertisements ”;

Base Finance Measures and Analysis ”; Previous Next You can create various measures in the data model to be used in any number of Power PivotTables. This forms the data modeling and analysis process with the data model using DAX. As you have learnt earlier in the previous sections, data modeling and analysis is dependent on specific business and context. In this chapter, you will learn data modeling and analysis based on a sample Profit and Loss database to understand how to create the required measures and use them in various Power PivotTables. You can apply the same method for data modeling and analysis for any business and context Creating Measures Based on Finance Data To create any financial report, you need to make calculations of amounts for a particular time period, organization, account, or geographical location. You also need to perform the headcount and cost per headcount calculations. In the data model, you can create base measures that can be reused in creating other measures. This is an effective way of data modeling with DAX. In order to perform calculations for profit and loss data analysis, you can create measures such as sum, year-over-year, year-to-date, quarter-to-date, variance, headcount, cost per headcount, etc. You can use these measures in the Power PivotTables to analyze the data and report the analysis results. In the following sections, you will learn how to create the base finance measures and analyze data with those measures. The measures are termed as base measures as they can be used in creating other financial measures. You will also learn how to create measures for the previous time periods and use them in the analysis. Creating Base Finance Measures In the finance data analysis, budget and forecast play a major role. Budget A budget is an estimate of a company’s revenues and expenses for a financial year. The budget is calculated at the beginning of a financial year keeping in view the company’s goals and targets. Budget measures need to be analyzed from time to time during the financial year, as the market conditions may change and the company may have to align its goals and targets to the current trends in the industry. Forecast A financial forecast is an estimate of a company”s future financial outcomes by examining the company’s historical data of revenues and expenses. You can use financial forecasting for the following − To determine how to allocate budget for a future period. To track the expected performance of the company. To take timely decisions to address shortfalls against the targets, or to maximize an emerging opportunity. Actuals To perform the budgeting and forecasting calculations, you require the actual revenue and expenses at any point in time. You can create the following 3 base finance measures that can be used in creating other financial measures in the data mode − Budget Sum Actual Sum Forecast Sum These measures are the aggregation sums over the columns – Budget, Actual, and Forecast in the Finance Data table. Create the base finance measures as follows − Budget Sum Budget Sum:=SUM(”Finance Data”[Budget]) Actual Sum Actual Sum:=SUM(”Finance Data”[Actual]) Forecast Sum Forecast Sum:=SUM(”Finance Data”[Forecast]) Analyzing Data with Base Finance Measures With the base finance measures and the Date table, you can perform your analysis as follow − Create a Power PivotTable. Add the field Fiscal Year from the Date table to Rows. Add the measures Budget Sum, Actual Sum, and Forecast Sum (that appear as fields in the PivotTable Fields list) to Values. Creating Finance Measures for Previous Periods With the three base finance measures and the Date table, you can create other finance measures. Suppose you want to compare the Actual Sum of a Quarter with the Actual Sum of previous Quarter. You can create the measure – Prior Quarter Actual Sum. Prior Quarter Actual Sum:=CALCULATE([Actual Sum], DATEADD(”Date”[Date],1,QUARTER)) Similarly, you can create the measure – Prior Year Actual Sum. Prior Year Actual Sum:=CALCULATE([Actual Sum], DATEADD(”Date”[Date],1,YEAR)) Analyzing Data with Finance Measures for Previous Periods With the base measures, measures for previous periods and the Date table, you can perform your analysis as follows − Create a Power PivotTable. Add the field Fiscal Quarter from the Date table to Rows. Add the measures Actual Sum and Prior Quarter Actual Sum to Values. Create another Power PivotTable. Add the field Fiscal Year from the Date table to Rows. Add the measures Actual Sum and Prior Year Actual Sum to Values. Print Page Previous Next Advertisements ”;

Forecast Measures and Analysis ”; Previous Next You can use Forecast measures to analyze the finance data and help an organization make necessary adjustments in its goals and targets for the year, to align the company’s performance to the changing business requirements. You need to update the forecasts regularly to keep up with the changes. You can then compare the most recent forecast to the budget for the rest of the period in the financial year so that the company can make the required adjustments to meet the business changes. At any time during a financial year, you can calculate the following − Forecast Attainment % Forecast Attainment % is the percentage of the forecast sum that you have spent to date, i.e. Forecast Attainment % = YTD Actual Sum/YTD Forecast Sum Forecast Unexpended Balance Forecast Unexpended Balance is the Forecast Sum remaining after the actual expenses, i.e Forecast Unexpended Balance = YTD Forecast Sum – YTD Actual Sum Budget Adjustment Budget Adjustment is the adjustment in the budget sum an organization needs to make (an increase or decrease) based on the forecast. Budget Adjustment = Forecast Unexpended Balance – Unexpended Balance The budget needs to be increased if the resulting value is positive. Otherwise, it can be adjusted for some other purpose. Creating Forecast Attainment Percentage Measure You can create Forecast Attainment Percentage measure as follows − Forecast Attainment Percentage:= IF([YTD Forecast Sum], [YTD Actual Sum]/[YTD Forecast Sum], BLANK()) Creating Forecast Unexpended Balance Measure You can create Forecast Unexpended Balance measure as follows − Forecast Unexpended Balance:=[YTD Forecast Sum]-[YTD Actual Sum] Creating Budget Adjustment Measure You can create Budget Adjustment measure as follows − Budget Adjustment:=[Forecast Unexpended Balance]-[Unexpended Balance] Analyzing Data with Forecast Measures Create a Power PivotTable as follows − Add Month from 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 Fiscal Year. Select FY2016 in the Slicer. Print Page Previous Next Advertisements ”;

Ending Headcount Measures ”; Previous Next You can create Ending Headcount measures for a specific period of time. The Ending Headcount is the sum of the people as on the last date in the specified period for which we have a non-blank sum of people. The Ending Headcount is obtained as follows − For a Month − Sum of People at the end of the specific Month. For a Quarter − Sum of People at the end of the last Month of the specific Quarter. For a Year − Sum of People at the end of the last Month of the specific Year. Creating Actual Ending Headcount Measure You can create Actual Ending Headcount measure as follows − Actual Ending Head Count:=CALCULATE(SUM(”Finance Data”[Actual People]),LASTNONBLANK(”Finance Data”[Date], IF(CALCULATE(SUM(”Finance Data”[Actual People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM(”Finance Data”[Actual People]), ALL(Accounts)))), ALL(Accounts)) DAX LASTNONBLANK function as used above returns the last date for which you have a non-blank sum of people so that you can calculate the sum of people on that date. Creating Budget Ending Headcount Measure You can create Budget Ending Headcount measure as follows − Budget Ending Head Count: = CALCULATE(SUM(”Finance Data”[Budget People]),LASTNONBLANK(”Finance Data”[Date], IF(CALCULATE(SUM(”Finance Data”[Budget People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM(”Finance Data”[Budget People]), ALL(Accounts)))), ALL(Accounts)) Creating Forecast Ending Headcount Measure You can create Forecast Ending Headcount measure as follows − Forecast Ending Head Count:= CALCULATE(SUM(”Finance Data”[Forecast People]), LASTNONBLANK(”Finance Data”[Date], IF(CALCULATE(SUM(”Finance Data”[Forecast People]), ALL(Accounts))=0, BLANK(),CALCULATE(SUM(”Finance Data”[Forecast People]), ALL(Accounts)))), ALL(Accounts)) Creating Prior Year Actual Ending Headcount Measuree You can create Prior Year Actual Ending Headcount measure as follows − Prior Year Actual Ending Headcount:=CALCULATE(”Finance Data”[Actual Ending Head Count], DATEADD(”Date”[Date],-1,YEAR)) Analyzing Data with Ending Headcount Measures Create a Power PivotTable as follows − Add the fields Fiscal Year and Month from the Date table to Rows. Add the measures Actual Ending Headcount, Budget Ending Headcount, Forecast Ending Headcount, Prior Year Actual Ending Headcount from Finance Data table to Values. Insert a Slicer on the Fiscal Year field. Select FY2016 in the Slicer. Print Page Previous Next Advertisements ”;

Variance Headcount Measures ”; Previous Next You can create the Variance Headcount measures based on the Headcount measures that you have created so far. Creating Variance to Budget Ending Headcount Measure You can create Variance to Budget Ending Headcount Measure as follows − VTB Ending Head Count:= ”Finance Data”[Budget Ending Head Count]-”Finance Data”[Actual Ending Head Count] Creating Variance to Budget Average Headcount Measure You can create Variance to Budget Average Headcount Measure as follows − VTB Average Head Count:= ”Finance Data”[Budget Average Headcount]-”Finance Data”[Actual Average Headcount Creating Variance to Budget Total Headcount Measure You can create Variance to Budget Total Headcount Measure as follows − VTB Total Head Count:= ”Finance Data”[Budget Total Headcount]-”Finance Data”[Actual Total Head Count] Creating Variance to Forecast Ending Headcount Measure You can create Variance to Forecast Ending Headcount Measure as follows − VTF Ending Head Count:= ”Finance Data”[Forecast Ending Head Count]-”Finance Data”[Actual Ending Head Count] Creating Variance to Forecast Average Headcount Measure You can create Variance to Forecast Average Headcount Measure as follows − VTF Average Head Count:= ”Finance Data”[Forecast Average Headcount]-”Finance Data”[Actual Average Headcount] Creating Variance to Forecast Total Headcount Measure You can create Variance to Forecast Total Headcount Measure as follows − VTF Total Head Count:= ”Finance Data”[Forecast Total Headcount]-”Finance Data”[Actual Total Head Count] Creating Forecast Variance to Budget Ending Headcount Measure You can create Forecast Variance to Budget Ending Headcount Measure as follows − Forecast VTB Ending Head Count:= ”Finance Data”[Budget Ending Head Count]-”Finance Data”[Forecast Ending Head Count] Creating Forecast Variance to Budget Average Headcount Measure You can create Forecast Variance to Budget Average Headcount Measure as follows − Forecast VTB Average Head Count:=”Finance Data”[Budget Average Headcount]-”Finance Data”[Forecast Average Headcount] Creating Forecast Variance to Budget Total Headcount Measure You can create Forecast Variance to Budget Total Headcount Measure as follows − Forecast VTB Total Head Count:= ”Finance Data”[Budget Total Headcount]-”Finance Data”[Forecast Total Headcount Print Page Previous Next Advertisements ”;

Rate Variance and Volume Variance ”; Previous Next You have learnt how to create measures for Annualized Cost Per Head and Total Headcount. You can use these measures to create Rate Variance and Volume Variance measures. Rate Variance measures calculate what portion of a Currency Variance is caused by differences in Cost Per Head. Volume Variance measures calculate how much of the Currency Variance is driven by fluctuation in Headcount. Creating Variance to Budget Rate Measure You can create Variance to Budget Rate measure as follows − VTB Rate:=([Budget Annualized CPH]/12-[Actual Annualized CPH]/12)*[Actual Total Head Count] Creating Variance to Budget Volume Measure You can create Variance to Budget Volume measure as follows − VTB Volume:=[VTB Total Head Count]*[Budget Annualized CPH]/12 Analyzing Data with Variance to Budget Measures Create a Power PivotTable as follows − Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows. Add the measures Actual Annualized CPH, Budget Annualized CPH, VTB Rate, VTB Volume, VTB Sum to Values. Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters. Select FY2016 in the Fiscal Year Filter. Select People in the Sub Class Filter. Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2. You can observe the following in the above PivotTable − VTB Sum value shown is only for Sub Class – People. For Fiscal Quarter FY2016-Q1, VTB Sum is $4,705,568, VTB Rate is $970,506,297, and VTB Volume is $-965,800,727. VTB Rate measure calculates that $970,506,297 of the Variance to Budget (VTB Sum) is caused by the difference in Cost per Head, and $-965,800,727 is caused by the difference in Headcount. If you add VTB Rate and VTB Volume, you will get $4,705,568, the same value as returned by VTB Sum for Sub Class People. Similarly, for Fiscal Quarter FY2016-Q2, VTB Rate is $1,281,467,662, and VTB Volume is $-1,210,710,978. If you add VTB Rate and VTB Volume, you will get $70,756,678, which is the VTB Sum value shown in the PivotTable. Creating Year-Over-Year Rate Measure You can create Year-Over-Year Rate measure as follows − YoY Rate:=([Actual Annualized CPH]/12-[Prior Year Actual Annualized CPH]/12)*[Actual Total Head Count] Creating Year-Over-Year Volume Measure You can create Year-Over-Year Volume measure as follows − YoY Volume:=[YoY Actual Total Headcount]*[Prior Year Actual Annualized CPH]/12 Creating Variance to Forecast Rate Measure You can create Variance to Forecast Rate measure as follows − VTF Rate:=([Forecast Annualized CPH]/12-[Actual Annualized CPH]/12)*[Actual Total Head Count] Creating Variance to Forecast Volume Measure You can create Variance to Forecast Volume measure as follows − VTF Volume:=[VTF Total Head Count]*[Forecast Annualized CPH]/12 Analyzing Data with Variance to Forecast Measures Create a Power PivotTable as follows − Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows. Add the measures Actual Annualized CPH, Forecast Annualized CPH, VTF Rate, VTF Volume, VTF Sum to Values. Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters. Select FY2016 in the Fiscal Year Filter. Select People in the Sub Class Filter. Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2. Creating Forecast Variance to Budget Rate Measure You can create Forecast Variance to Budget Rate measure as follows − Forecast VTB Rate:=([Budget Annualized CPH]/12-[Forecast Annualized CPH]/12)*[Forecast Total Headcount] Creating Forecast Variance to Budget Volume Measure You can create Forecast Variance to Budget Volume measure as follows − Forecast VTB Volume:=[Forecast VTB Total Head Count]*[Budget Annualized CPH]/12 Analyzing Data with Forecast Variance to Budget Measures Create a Power PivotTable as follows − Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows. Add the measures Budget Annualized CPH, Forecast Annualized CPH, Forecast VTB Rate, Forecast VTB Volume, Forecast VTB Sum to Values. Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters. Select FY2016 in the Fiscal Year Filter. Select People in the Sub Class Filter. Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2. Print Page Previous Next Advertisements ”;

Data Modeling with DAX – Useful Resources ”; Previous Next The following resources contain additional information on Data Modeling with DAX. Please use them to get more in-depth knowledge on this. Mastering DAX And Data Models In Power BI Desktop 54 Lectures 5.5 hours Abhay Gadiya More Detail Business Intelligence Course With Microsoft Power BI Best Seller 137 Lectures 8.5 hours Pavan Lalwani More Detail Data Science Bootcamp with Power BI and Python Best Seller 52 Lectures 3.5 hours Harshit Srivastava More Detail Print Page Previous Next Advertisements ”;