- Data Modeling with DAX Tutorial
- Home
- Data Modeling with DAX - Overview
- Data Modeling with DAX - Concepts
- Data Modeling Using Excel Power Pivot
- Loading Data into the Data Model
- Defining Data Types in the Data Model
- Understanding Data Tables
- Extending the Data Model
- Base Finance Measures & Analysis
- YoY Finance Measures & Analysis
- Variance Measures & Analysis
- Year-to-Date Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Budget Measures & Analysis
- Forecast Measures & Analysis
- Count of Months Measures
- Ending Headcount Measures
- Average Headcount Measures
- Total Headcount Measures
- YoY Headcount Measures & Analysis
- Variance Headcount Measures
- Cost Per Headcount Measures & Analysis
- Rate Variance & Volume Variance
- Data Modeling with DAX Resources
- Quick Guide
- Useful Resources
- Discussion
Count of Months Measures
You can create the Count of Months measures that can be used in creating Headcount measures and Cost Per Head measures. These measures count the distinct values of Fiscal Month column where the Actual column / Budget column / Forecast column has non-zero values in the Finance Data table. This is required because the Finance Data table contains zero values in the Actual column and those rows are to be excluded while calculating Headcount and Cost per Head.
Creating Count of Actual Months Measure
You can create Count of Actual Months measure as follows −
CountOfActualMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Actual]<>0)
Creating Count of Budget Months Measure
You can create Count of Budget Months measure as follows −
CountOfBudgetMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Budget]<>0)
Creating Count of Forecast Months Measure
You can create Count of Forecast Months measure as follows −
CountOfForecastMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Forecast]<>0)