Show last 6 months of data in a table

[mbrierley]

I have the following table, but what I really want is to split the '17 week average' column up into 6 i.e. one column for each of the last 6 months. Ideally, this would automatically update according to the current date. The dates are currently formatted to be like September 2020. 

I know that I can do this in a matrix, but I would like to include both 'Name (Staff ref)' and 'Line Manager' columns in my table

[richbenmintz]

Hi @mbrierley,

Are you able to provide some sample data or a sample pbix file?

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[mbrierley]

@richbenmintz I'm not as proficient at PowerBI as I am in Excel, so I've managed to recreate it using the latter. On the left side is what I want it to look like. On the right is how the raw data is set up. I'm trying to show the average working time for employees over the course of the last 6 months.

As mentioned previously, if I didn't need to include a line manager column, I would use a matrix, but I need to show this. I know one option would be to concatenate the contents of the name and line manager columns, and that is an option, but I'd prefer not to in case users want to filter by line manager.

Thanks

cc @amitchandak @sevenhills 

[amitchandak]

@mbrierley , not very clear.

For last 17 weeks you need following

column in date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
measure

Last 17 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-17 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

for last 6 month with date table

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[sevenhills]

Check this article

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...