cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayp
Regular Visitor

shifting results to align based on start month

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

6 REPLIES 6
asocorro
Skilled Sharer
Skilled Sharer

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

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
mayp
Regular Visitor

 

"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
Skilled Sharer
Skilled Sharer

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:

 

f.jpg

 

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

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
mayp
Regular Visitor

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
Skilled Sharer
Skilled Sharer

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

 

rn.jpg

 

 

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

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

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

 

Anyone else solve the conundrum?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors