Quarter-to-Date Measures and Analysis

To calculate a result that includes a starting balance from the beginning of a period, such as a fiscal quarter, 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 Quarter-to-Date measures and how to carry out data analysis with the same.

Creating Quarter-to-Date Sum Measure

Create Quarter-to-Date Actual Sum measure as follows −

QTD Actual Sum:=TOTALQTD([Actual Sum], Date [Date],ALL( Date ))

Creating Quarter-to-Date Budget Sum Measure

Create Quarter-to-Date Budget Sum measure as follows −

QTD Budget Sum:=TOTALQTD([Budget Sum], Date [Date], ALL( Date ))

Creating Quarter-to-Date Forecast Sum Measure

Create Quarter-to-Date Budget Sum measure as follows −

QTD Budget Sum:=TOTALQTD([Budget Sum], Date [Date], ALL( Date ))

Creating Quarter-to-Date Forecast Sum Measure

Create Quarter-to-Date Forecast Sum measure as follows −

QTD Forecast Sum:=TOTALQTD([Forecast Sum], Date [Date], ALL( Date ))

Creating Prior Quarter-to-Date Actual Sum Measure

Create Prior Quarter-to-Date Actual Sum measure as follows −

Prior QTD Actual Sum:=TOTALQTD([Prior Quarter Actual Sum], Date [Date], ALL( Date ))

Analyzing Data with Quarter-to-Date Measures

Create a Power PivotTable as follows −

Add Fiscal Month from Date table to Rows.

Add the measures Actual Sum, QTD Actual Sum, QTD Budget Sum, and QTD Forecast Sum from Finance Data table to Values.

Insert a Spcer on the Fiscal Quarter from the Date table.

Select FY2016-Q2 in the Spcer.

Create a Power PivotTable as follows −

Add Fiscal Month from Date table to Rows.

Add the measures Actual Sum, QTD Actual Sum, Prior Quarter Actual Sum, and Prior QTD Actual Sum from Finance Data table to Values.

Insert a Spcer on the Fiscal Quarter from Date table.

Select FY2016-Q1 in the Spcer.