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 Intelpgence 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 Spcer on the Fiscal Year from the Date table.

Select FY2016 in the Spcer.

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 Spcer on the Fiscal Year from the Date table.

Select FY2016 in the Spcer.