# PBI Best Practices - Preferred DAX Formulas

## Last Period Indicators (Last week, Last year)

There are several ways of computing the last periods, including PREVIOUS YEAR... those functions are compatible with Indexima.

However, in terms of performance, Indexima recommends using another way:

CODE
``````CALCULATE(
VAR MaxYear=MAX(TimeTable[year]))
VAR SelectedYear = SELECTEDVALUE(TimeTable[year],MaxYear)
RETURN
CALCULATE( myfacttable[myIndicator], TimeTable[year]= SelectedYear -1
)``````

source: Dynamic Last Week Indicator

## YearToDate, MonthToDate, QuarterToDate

PowerBI provides easy to use Formulas to compute those indicators

CODE
``````_viaTOTALxTD_YTDSales = TOTALYTD(SUM(fact_sales[salesamount]),'calendar'[date])
_viaTOTALxTD_QTDSales = TOTALQTD(SUM(fact_sales[salesamount]),'calendar'[date])
_viaTOTALxTD_MTDSales = TOTALMTD(SUM(fact_sales[salesamount]),'calendar'[date])``````

However, using Indexima, we recommend using the following formulas instead. (This will require the creation of a date Dimension hosted in indexima instead of the one in PowerBI)

### 'Current Year' Indicators

YearToDate

CODE
``````_CY_YTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_year] = MAX ( 'calendar'[calendar_year]  )
&& 'calendar'[date]<= MAX ( 'calendar'[date] )
)
)``````

QuarterToDate

CODE
``````_CY_QTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearquarter] = MAX ( 'calendar'[calendar_yearquarter]  )
&& 'calendar'[date]<= MAX ( 'calendar'[date] )
)
)``````

MonthToDate

CODE
``````_CY_MTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearmonth] = MAX ( 'calendar'[calendar_yearmonth]  )
&& 'calendar'[date]<= MAX ( 'calendar'[date] )
)
)``````

WeekToDate

CODE
``````_CY_WTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearweek] = MAX ( 'calendar'[calendar_yearweek]  )
&& 'calendar'[date]<= MAX ( 'calendar'[date] )
)
)``````

### 'Last Year' Indicators

LY YearToDate

CODE
``````_LY_YTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_year] = MAX ( 'calendar'[calendar_year]  )-1
&& 'calendar'[date]<= MAX ( 'calendar'[same_daylastyear] )
)
)``````

LY QuarterToDate

CODE
``````_BestPractIndexima_QTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearquarter] = MAX ( 'calendar'[calendar_yearquarter]  )-100
&& 'calendar'[date]<= MAX ( 'calendar'[same_daylastyear] )
)
)``````

LY MonthToDate

CODE
``````_BestPractIndexima_MTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearmonth] = MAX ( 'calendar'[calendar_yearmonth]  )-100
&& 'calendar'[date]<= MAX ( 'calendar'[same_daylastyear] )
)
)``````

### 'Previous Week' Indicators

WeekToDate

CODE
``````_CY_WTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearweek] = MAX ( 'calendar'[Calendar_YearPreviousWeek]  )
&& 'calendar'[date]<= MAX ( 'calendar'[SameDayPreviousWeek] )
)
)``````

#### 'Last Year' Indicators when choosing Y-1 or Y-2

Additional steps to perform in PowerBI

• Creation of an Import table
• named "Relative Year"
• with the Fomula: Relative Year = DATATABLE("RelativeYear",STRING,{{" Y-2 "},{" Y-1 "}})
• with 2 Measures
• Last Year Value = SELECTEDVALUE('Relative Year'[RelativeYear]," Y-1 ")
• LYEAR = if([Last Year Value] = " Y-1 ",1,2)

This will result as below

• Additional Column in Calendar Table1 (Date Dimension Table materialized in Indexima)
• SameDayLY = if( 'Relative Year'[Last Year Value]="-1",'calendar'[same_daylastyear],'calendar'[same_daylast2year])

(1): and not in the Relative Year Table

The 'Last Year' indicator is modified by using the column SameDayLY and the measure LYEAR

LY with Year as Variable QuarterToDate

CODE
``````_LY_RelYear_QTDSales = CALCULATE (
sum(fact_sales[salesamount]),
FILTER (
ALL ( 'calendar'),
'calendar'[calendar_yearquarter] = MAX ( 'calendar'[calendar_yearquarter]  )-'Relative Year'[LYEAR]*100
&& 'calendar'[date]<= MAX ( 'calendar'[SameDayLY] )
)
)``````
JavaScript errors detected