When we use the DAX function CALCULATE and give it the filter values as arguments as a result we will get a Power Pivot report filled with rows containing the same values. Although these values correspond to the given filter this way of displaying data is not the best solution. To make report neat, we can use the FILTER function or, even better, the KEEPFILTERS function about which you will learn more if you read the text that follows …
Reports often require that in addition to the usual review of the sales we create running total, i.e. to track cumulative growth of sales from beginning of the month, quarter or year. For this purpose we use the Time Intelligence functions DATESMTD, DATESQTD and DATESYTD by which we can track cumulative sales growth since the beginning of the given period. If you want to find out more please read the recipe that follows…
Time Intelligence functions are often used for reviewing implementation of the goals at the same time last year. In what follows we will mention a few of DAX functions that cover this area. First, there is a function SAMEPERIODLASTYEAR which should monitor business results in the same period last year. And then there are functions DATEADD and PARALLELPERIOD by which we can monitor the achievement of objectives in the previous or next period (month, quarter, or year) in relation to the current data.
If we want to carry out calculations in previous or the next period in relation to the current date, should use “PREVIOUS” and “NEXT” Time Intelligence function. They allow aggregation of data, usually the column that contains the quantity or amount of sales, compared to the previous year, quarter, month or day, as well as in relation to the next year, quarter, month or day. In the example below you will see how to utilize this class of DAX functions.
CALCULATE is one of the most commonly used functions in DAX. It is used to calculate expressions, usually given by some aggregation function, with use of one or more filters. It is not incorrect to use CALCULATE without a filter, but that’s not much point. The strength of this function is the ability of combining expression with a range of DAX functions that narrow set of data to perform aggregation with…