Optimizing the Power BI model

While working your way through the measures discussed in this chapter, you may have noticed that we do a lot with the YearMonthCtr column in the Calendar table. Additionally, we do a lot of translations from a project's start date to the corresponding month counter.

Because of this, we have an opportunity to further optimize our solution, while making it easier to understand and maintain, by doing this translation beforehand. This is a case where a calculated column could indeed be appropriate – although it would still be good to consider creating this column using Power Query.

For now, let's create a calculated column in fProjectSales that contains the YearMonthCtr value corresponding to the project's start date:

StartCtr =
VAR ThisStartDate = [StartDate]
RETURN
CALCULATE(
    MAX('Calendar'[YearMonthCtr]),
    'Calendar'[Date] = ThisStartDate
)

With the new StartCtr column, many of the formulas...