MAX

Obtaining the last month of a calendar

In this scenario, we start with a calendar and we want to obtain the last month included in it, regardless of the filters that may be applied to the data model. In our example, this calendar will include, in addition to the date field, a field with the year, month... and a text field in which the year and month of each date have been concatenated (in text format), ensuring that the result has 6 characters in length (or, in other words, ensuring that the month is represented with two digits). This can be done in the query editor by creating a custom column with the following M code.

Submitted by admin on Thu, 01/12/2023 - 18:54

Emulación de la función TOTALYTD

The TOTALYTD function is easy to use and allows us to quickly calculate the result of an expression for the period between January 1st and the current context's last date, but sometimes we need more control over the evaluated expression. In this scenario, we will simulate the TOTALYTD function using other functions that would allow us this additional control.

We start with a table called Movements where we find financial movements associated with a date (we will assume that these are sales):

Submitted by admin on Mon, 01/09/2023 - 09:46

Calculation of accumulated totals

A common scenario is one in which we want to evaluate the accumulated total, including all data up to a certain date and not just those involved in the current filter context. For example, if we assume the existence of a sales table Sales that contains an Amount column with the sales figure, we could define a measure that calculates the sum of this column with the following DAX expression:

Submitted by admin on Sun, 01/08/2023 - 11:10

Access to the dates of the current context

Assuming we have a calendar table ("Calendar") and a "Date" field containing the dates, we can access the dates involved in the current context through said field. This allows us to access the first day, the last day, etc. of the period involved in the current context using DAX functions and simple arithmetic operations.

Thus, for example, we can access the first day of the current context by calculating the minimum value of the date column with the MIN function, as follows:

Submitted by admin on Wed, 06/26/2019 - 16:39
Subscribe to MAX