Uso de variables para optimizar fórmulas en DAX

How to create business management indicators in Power BI?

Power BI is a powerful tool that allows the creation of business management indicators in an efficient way. One of its best-kept secrets is the use of variables, which facilitate the simplification of complex formulas. Through Year over Year measures, the generation of indicators becomes not only more understandable but also more manageable. In the following, we will explore how to create these indicators using measures and variables.

What is a Year over Year measure?

The Year over Year (YOY) measure calculates the growth or decline of a specific variable or metric compared to the same period of the previous year. This indicator is essential for analyzing long-term trends in sales data.

Here's how to create the YOY in Power BI:

1. Creating a new measure:

   • Right click on the measure space, select "new measure".

2. Using variables:

   • Define a variable to store the formula:

     VAR SalesLastYear = CALCULATE(TotalSales, DATEADD(DATE, -1, YEAR))

3. Year over Year calculation:

   • Perform the division using the DIVIDE function to avoid division by zero errors:

     RETURN DIVIDE(TotalSales - SalesLastYear, SalesLastYear, 0).

How do we differentiate a weekend in Power BI?

Differentiating the days of the week is essential for certain analyses, such as calculating the average sales over the weekend.

1. Creating a new column:

   • Within the DimDates dimension, right click and select "new column".
   • Use a formula to get the name of the day:

     DayName = FORMAT(DimDates[Date], "dddd").

2. Weekend identification:

   • Create a measure using filters to identify Saturdays and Sundays:

     VAR WeekEnd = FILTER(DimDates, DimDates[DayName] = "Saturday" || DimDates[DayName] = "Sunday").

3. Calculation of average sales:

   • Use the AVERAGEX function to calculate the average sales over the weekend:

     RETURN AVERAGEX(Weekend, Sales)

     .

How to calculate the Quarter over Quarter?

The next logical step after implementing YOY is to calculate the quarterly change, known as Quarter over Quarter (QOQ). It works similarly to YOY but applies the comparison to the previous quarter.

1. New measures and variables:

   • Follows a similar process to YOY, using variables to store data from the previous quarter with the DATEADD function.

2. Community Invitation:

   • Once calculated, sharing this metric with the Power BI user community encourages collaborative learning.

So now you know, variables not only simplify measurements in Power BI but also make your data analysis more robust. We encourage you to keep exploring and sharing your findings to grow with the community.