Calculations and Measures | CLEARIFY

Calculations Using Something Called DAX

QQube does all of this for you out-of-the-box. 

This guide is for informational purposes only, with additional links for advanced users.

In order to calculate a sum of an amount or quantity for a particular time period in a pivot table, you must use create a calculation as a separate field.  Regular Pivot Tables do not give you the ability to do this.

PowerPivot (and Power BI) uses a specific Microsoft methodology called DAX (Data Analysis Expressions) to achieve this result.

In this example there are three DAX formulas for  "Current MTD", "Previous MTD", "Previous Year Month" so that they could co-exist with each other

QQube Does the Hard Part

The DAX language can be very complex, even for the most advanced user.  So QQube came to the rescue and added over 5,000 DAX formulas.   They are based upon the work of the world renowned DAX experts, Marco Russo and his partner Alberto Ferrari;  which means that our methodology is based upon public - and not proprietary - knowledge.

It also means that you have the most efficient and effective measures available - anywhere.

Available measure types:

.Base Measures. These are measures that are prefixed with a period (".") and are generally a summation of the raw QQube fields.  There are no filters in these measures and are available for use for any data model.

Intermediate Measures. These are internal measures to assess today's date or last date in a list of transactional data and designed to be used in other measures.  They are hidden on purpose.

Hierarchy Measures. Present in financial statement, job profit and loss, and trial balance related data models (including Profit and Loss Detail) to address ragged hierarchies of financial groupings and account levels. They are also designed to be used in other measures, and hidden on purpose.

📅 Calendar Pattern Measures. These allow for easy graphing of Cumulative by Year, Quarter, Month, Week.  For Power Pivot they are great for seeing columns of like data side by side, such as year, or month and will give proper grand total columns.

Calendar Specific Measures. Whereas Calendar Pattern Measures (📅) allow for equal buckets over a long period of time, Specific Calendar Measures speak to a specific and narrow period of time, e.g. Month-To-Date, Year-To-Date, Last Month, Yesterday, etc.

Comparison Measures. Subtracting one specific calendar measure from another, % difference, to make it easier for the end-user. 

Single Value Measures. Specific measures which don't follow a calendar pattern, nor are segmented into specific calendar buckets. e.g. Days out of Stock.

Advanced Materials

DAX Methodology