- 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
Year-to-Date Measures and Analysis
To calculate a result that includes a starting balance from the beginning of a period, such as a fiscal year, up to a specific period in time, you can use DAX Time Intelligence functions. This will enable you to analyze data on a month level.
In this chapter, you will learn how to create Year-to-Date measures and how to carry out data analysis with the same.
Creating Year-to-Date Actual Sum Measure
Create Year-to-Date Actual Sum measure as follows −
YTD Actual Sum:=TOTALYTD([Actual Sum], 'Date'[Date], ALL('Date'), "6/30")
Creating Year-to-Date Budget Sum Measure
Create Year-to-Date Budget Sum measure as follows −
YTD Budget Sum:=TOTALYTD([Budget Sum], 'Date'[Date], ALL('Date'), "6/30")
Creating Year-to-Date Forecast Sum Measure
Create Year-to-Date Forecast Sum measure as follows −
YTD Forecast Sum:=TOTALYTD([Forecast Sum], 'Date'[Date], ALL('Date'), "6/30")
Creating Prior Year-to-Date Actual Sum Measure
Create Prior Year-to-Date Actual Sum measure as follows −
Prior YTD Actual Sum:=TOTALYTD([Prior Year Actual Sum], 'Date'[Date], ALL('Date'), "6/30")
Analyzing Data with Year-to-Date Measures
Create a Power PivotTable as follows −
Add Month from Date table to Rows.
Add the measures Actual Sum, YTD Actual Sum, YTD Budget Sum, and YTD Forecast Sum from the Finance Data table to Values.
Insert a Slicer on the Fiscal Year from the Date table.
Select FY2016 in the Slicer.
Create a Power PivotTable as follows −
Add Month from Date table to Rows.
Add the measures Actual Sum, YTD Actual Sum, Prior Year Actual Sum, and Prior Year YTD Actual Sum from the Finance Data table to Values.
Insert a Slicer on the Fiscal Year from the Date table.
Select FY2016 in the Slicer.
