Percentage Change over Last Month - Can't Pull Last Month's Sales Figures

[dentonblake]

Not pulling Last Month's sales in order to calculate %'age change (or variance)

 Three Measures:

1.) Current Month = TOTALMTD(Sum(TABC[total_receipts]), TABC[obligation_end_date_yyyymmdd],ALL(TABC[obligation_end_date_yyyymmdd]))

2.) Previous Month = CALCULATE(TABC[Current Month],DATEADD(TABC[obligation_end_date_yyyymmdd], -1, Month))

3.) Percentage Change = (TABC[Current Month]-TABC[Previous Month])/if(isblank(TABC[Previous Month]),TABC[Current Month],TABC[Previous Month])

Table Example:

Restaurant Name   Obligation End Date Current Month     Previous Month

Restaurant 1          2017   November      $2,000

Restaurant 1          2017   October          $2,200

Restaurant 2          2017   November      $3,500   

Etc...

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[dentonblake]

Link to snapshot of data -> https://retailstreet.box.com/s/w96ae6bj1bgkkwhx2t4r8w20gbv6qm17

[Rfranca]

Hi, dentonblake

try

SLI_VOLUME = SUM(ft_SELLIN[QUANTIDADE]))

xMonthActual_SLI = TOTALMTD([SLI_VOLUME];dm_CALENDAR[Date])

xMonthPreviousToDate_SLI = TOTALMTD([SLI_VOLUME];DATESMTD(DATEADD(dm_CALENDAR[Date];-1;YEAR)))

xPercent_MTDActualvsLastYear = IFERROR( DIVIDE([xMonthActual_SLI];[xMonthPreviousToDate_SLI];0);0)

[dentonblake]

Thank you.

Do I need to create a Calendar table?  If so, what is the relationship between the two tables?

[Rfranca]

YES! you Do I need to create a Calendar table.

The relationship between the two tables is dm_CALENDAR[Date] with TABC[obligation_end_date_yyyymmdd] or another field that relates the sales date.

Whenever you use KPIs with respect to time, you should create a CALENDAR table. With it you can create other time-related KPIs like YTD, YTG, YoY MTD and others.

[dentonblake]

Screenshot: