[PowerPivot] Calculate percentages in 15 minutes intervals of last X worked Days

[Bullstrik1]

Hi all!


First of all, I would like to let you all know that i'm fairly new with powerpivot/powerquery and i'v been reading Rob Collie's book.
Although this book has been a great help, i'v been eager to get a solution to my problem this last few weeks (cause its bugging me like hell night and day ), and thats why i'm here asking for your help.


I would like to do the folowing calculations with my data:


Calculate the percentage of incoming calls, that arrive to a call center, in 15 minute interval considering, only, the most recent 28 worked days(this excludes national holidays and weekends).

Please take in consideration that my data will "grow", since i will be updating a folder with files that will populate my powerpivot via some powerquerys that i developed, and so the DAX formula produced must be flexible in order to get the "moving" date ranges...

As i read in Rob's book, its advisable to create a separate table with dates and so i did: i created a separate excel workbook with dates and holidays dates, and i managed to get a proper column, in the dates sheet, with the worked dates (1's) and non worked dates(0's). In this workbook, I also created a sheet with the lower and upper boundaries of my 15 minute interval for each hour period in a workday. This workbook was properly imported to the powerpivot in another workbook (my powertests workbook). In this workbook i managed to calculate all the proportions i wanted but using all of my data (almost a full year of data) witch is not what i want, but its almost :P


Here is a pic of the tables existing in my data model:

And here is a picture showing what data i would like to retrieve via a DAX formula in powerpivot for the most recent 28 worked days:

Please remember what i want to calculate:
Calculate the percentage of incoming calls, that arrive to a call center, in 15 minute interval considering, only, the most recent 28 worked days(this excludes national holidays and weekends).

Also, i'v read this post and this one too... i guess i didn't got the point on those because i think i got a similar problem :P

Can someone please lend me a hand on this ?
Its really bugging me

If you need the workbook for further analysis please let me know.
I honestly appreciate any help i could get resolving this problem.
Cheers all

 

[Bullstrik1]

I replied to this last night, but something must have gone wrong - sorry about that. It is possible to extract a value from a row/column in Power Query and use it in another query. I would do this (assuming you have your sales data in PQ already).

1. Create a new query referencing the sales query
2. Remove all columns other than the date column
3. Remove duplicates
4. Sort largest to smallest
5. Keep top rows (1). This will give you 1 row, 1 column containing the last date
6. Save the query
7. Create your calendar table using the approach discussed above
8. Reference the query from step 6 to extract the date you need. Read about how to do that at this post Share and Refresh Power Query with Local Links - PowerPivotPro Look for the part about "Very Important Insight" and Record.Field half way down the page
7. Once you have the date inside your calendar query, you can then use it in your formula as the last date for your calendar

The only thing I am not 100% sure about is which query will refresh first. You need the sales query to refresh first otherwise the dates will be out of whack. I have a feeling you can't control this

Hi all!

Matt, no worries mate! I belive those "lost" hours of work are the ones that get us to know the tools the best way possible, sooner or later.

Anyway the powerquery code that i used to get the last date with data from my data table was as folow (just the first lines are important, the rest of the code its all about Matt's way of building an automated calendar table in powerquery):

 let[COLOR=#0000ff] LastDate= Record.Field(Table.Last(ImportExcelFiles), "Date"),[/COLOR]
 Source = List.Dates,
 #"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date([COLOR=#0000ff]LastDate[/COLOR]) - #date(2015,1,1))+1, #duration(1, 0, 0, 0)),
 #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
 #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
 #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
 #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
 #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
 #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
 #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
 #"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
 #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
 #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
 #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
 #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
 #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
 #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
 #"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q"& Number.ToText([Quarter Number],"00")),
 #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Short Year", "Day"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns1",{"Index", "Quarter Year", "Year", "Quarter Number", "Month Number", "Month Name", "Day Name", "Date"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Quarter Year"}),
    #"Added Custom7" = Table.AddColumn(#"Removed Columns2", "WorkDay", each if Date.DayOfWeek([Date])=1 then 0 else if Date.DayOfWeek([Date])=7 then 0 else 1)
in
    #"Added Custom7"

This calendar table will always be automated with the last data that i will retreive via the query "ImportExcelFiles", witch will be automaticlly refresh when workbook opens (i defined on data>connections to be like this). In the future i will need a calendar tables with the dates updated one week ahead as of today(), in order to build my forecasts, but i belive it will be easier to get that job done when the time comes :P

Tanks again for the help! Lets see if this will work

 

[ImkeF]

This will give you one week ahead: = Date.AddWeeks(DateTime.FixedLocalNow(), 1)

if you wrap it in = Date.EndOfWeek(...) you can iterate by whole weeks

BTW: I'd recommend using List.Max(ImportExcelFiles[Date]) instead of Record.Field(Table.Last(ImportExcelFiles), "Date") - so you don't rely on the correct sort order of your table (as the Date-column already has the needed sort order in it)

 

[Bullstrik1]

This will give you one week ahead: = Date.AddWeeks(DateTime.FixedLocalNow(), 1)

if you wrap it in = Date.EndOfWeek(...) you can iterate by whole weeks

BTW: I'd recommend using List.Max(ImportExcelFiles[Date]) instead of Record.Field(Table.Last(ImportExcelFiles), "Date") - so you don't rely on the correct sort order of your table (as the Date-column already has the needed sort order in it)

Hi everyone!
My apologies for the late response but this last few days had been a blast for me.

On the topic: ImKeF i tried your sugestion, as legit as it sounded me, but seems like powerquery didn't like List.Max - i had to use Table.Max since ImportExcelFiles is a powerquery table and not a list. I also tried List.Max and didn't work. In any case tank you very much for your feedback

I now have the problem solved. I can calculate the % of calls, gathered in last 28 days, per 15 minute intervals. I used Matt's sugestion to get the job done.
What I'm not lieking to much is the time Excel takes on loading 45 files of data that results in 709k distinct records. It takes like 10 minutes to update all my connections and perform a "simple" calculation: % of calls, gathered in last 28 days, per 15 minute intervals.

Isn't this too much time? Maybe my querys could be optimized or something...

Anyway, tank you all for your help!
Hepo this post can help someone else in the future.

Cheers all