Sales Percentage Change Current Month over Same Month Last Year

[dentonblake]

Power BI All Stars:

I have 10 years worth of sales data - seeking help with calculating the percentage change for current month (November 2017) over same month last year (November 2016).  Also, is there a way to calculate YTD %'age change - e.g., Jan - Nov 2017 versus Jan - Nov 2016?

Thank you for helping a Power BI novice!  ~DentonBlake

[Ashish_Mathur]

Hi,

You may use the CALCULATE() and SAMEPRIODLASTYEAR() functions.  To get more specific help, share some data.

[dentonblake]

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

[Ashish_Mathur]

Hi @dentonblake,

I do not understand your data.  Why are the Year, Quarter. Month and Day columns being repeated in your dataset?

[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)

[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]

Thank you, again, Rfranca ... very helpful!

Can the calendar table have one column for the date or does it require multiple columns for month, year, quarter, etc...?

~db

[Rfranca]

A CALENDAR table has several columns all of these columns help in calculations of various periods like month, year, quarter, week, day among others. and also has columns for the presentation of these periods in dashboards, something like JANUARY, JAN, Q1, MONDAY, etc.

So a CALENDAR tabela should be very complete according to your needs. Another important point is the start and end of the date range, that is the first date and the last date being that this interval should be compatible with your sales data.

On the internet you have some ready examples of table CALENDAR

[dentonblake]

Still doing something wrong ... I wonder if it's the relationship with the Date (Calendar) Table?

Not pulling data for xMonthActual or xMonthPreviousToDate.

Thank you, ~db

[dentonblake]

[Rfranca]

Let's see what's wrong ...

1. There is a CALENDAR table (dm_CALENDARY)
2. There is a field in this CALENDAR table called [Date] (dm_CALENDAR [Date])
3. This field is this date format;
4. There is a data table that you called TBC;
5. There is a date (sales) field in this TBC table which must be the field TABC [obligation_end_date_yyyymmdd]
6. This field is this date format;
7. There is a relationship between the dm_CALENDAR [Date] table fields with TABC [obligation_end_date_yyyymmdd]
8. All field dates in the TABC table [obligation_end_date_yyyymmdd] exist in the table field dm_CALENDAR [Date]

All of the above should be ok.

For the image sent the TABC [obligation_end_date_yyyymmdd] field is in the wrong format, it is this date-time and should be just date.

try again now

[dentonblake]

Changing the date format worked (thank you), however, the %'age change measure is causing an error - screenshot below:

Also, is there a way to consolidate data on one row for each Location_Name?

Thank you!  ~db

[dentonblake]

%'age change measure results in 0.00% - any suggestions?  Thank you, ~db

[dentonblake]

All measures are working now - last question:  how do I filter so only the "comparison row" is showing (top row in the image below).  Thank you, ~db

[Rfranca · Accepted solution]

Dentonblake

1. Exclude from the table the columns [total_recipets], [TABC_SALES], [Quarter], [Day]

Do not forget that the KPI you created is monthly, any column you use can lead to inconsistencies in the data.
And the most appropriate filter is YEAR.
Include a YEAR SEGMENTATION.

It will get better.

if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca

[dentonblake]

Rfranca-

Thank you so much - I think I'm getting closer to the desired result. 

Is there a way to consolidate the data on one row - see below?  Also, since I'm new to Power BI, I don't think if I fully understand "YEAR SEGMENTATION" ... is this a new measure I should add?

 Best,  ~db

[dentonblake]

Got it!  Only took an entire weekend - HA!

Thank you for all of the assistance!

~db

[v-huizhn-msft]

Hi @dentonblake,

Based on my understanding, please create the measure using the formula and check if it works fine.

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

Please feel free to ask if you have any other issue.

Best Regards,
Angelia

[dentonblake]

Angelia, thank you for your response.  When I activate or check the measure field for this calculation it processes for about 10 - 15 minutes and then generates an error.   ~db