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 Intelligence 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 Slicer on the Fiscal Quarter from the Date table.

  • Select FY2016-Q2 in the Slicer.

FY2016-Q2

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 Slicer on the Fiscal Quarter from Date table.

  • Select FY2016-Q1 in the Slicer.

FY2016-Q1
Advertisements