shifting results to align based on start month

[mayp]

Hello all,

I am creating a benchmarking report for a group of franchise owners (FO's).  The data comes from a common system so it is very clean and the different FO's are easily identified.  I would like allow the reports/visulaizations to align the data by FO start month so that FO #100 who started in 04-2015 can compare the results of his first month to all the other FO's first month results who all started in on different dates (as one simple example).  I have a date table with exhaustive date columns. 

I'm pretty new at BI so assume you are talking to a 4th grader.

thanks in advance.  Phil

[asocorro]

What's your goal, separate charts for each FO or all together in one?  What kind of chart do you have in mind?

[mayp]

"What's your goal, separate charts for each FO or all together in one?"

Both.  There will be reports/visualizations where the FO can look at his own results (think traditional business reporting), and benchmarking reports where he can compare the his results against others.  I don't envision these being on the same page as the slicing wouldn't work well.

"What kind of chart do you have in mind?"

One example... showing revenue by month (or week, day) on a line chart for many of FO's where the x-axis starts with month 1 (instead of the actual date in which the FO starts.) 

Without this shift, every FO's line would start rising from zero in the month they started business.  I would like to shift to a common start month so that comparisons can be made. 

I would like to build this logic into the dataset so that it can be swaped out with "mm-yyyy" for any visulization/report.

thanks,

[asocorro]

Perhaps a solution is to assign a sequential number to each sales figure, by FO, to indicate what month number it is for.  Then you can align them by this number.  For example, you would add a "Sales Month Index" to your data:

If using SQL Server, you could calculate this number in your query using a Window Function.  Otherwise in M (Power Query).

[mayp]

Asocorro, you are tracking with me. 

I would think that the source of this info is an element within a FO table where I would capture FO specific data (e.g. location, name, start date, start month, address, etc...)  I would think the "sales month index" as you show would not housed be in the transactional data table unless by calculation or some kind of automation. 

So, what the work flow would be to put the start date in one location, and let M or a calculated field populate the "sales month index" .    The how of accomplishing that is what I am looking for.  thanks.

[asocorro]

Offhand, I can tell you the SQL Server query to achive it (using Adventure Works):

For the M or DAX solution I need to experiment a little to figure it out.

[genna_rwsc]

@asocorro were you able to figure out the M or DAX?

Anyone else solve the conundrum?