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



## Bullstrik1 (Dec 10, 2015)

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


----------



## Abhay Gadiya (Dec 11, 2015)

Please share your data so that I can have a look at it. It is difficult to work on this screenshots. You can upload something in One drive or Drop box public folder and provide a link here.


----------



## Bullstrik1 (Dec 11, 2015)

Abhay Gadiya said:


> Please share your data so that I can have a look at it. It is difficult to work on this screenshots. You can upload something in One drive or Drop box public folder and provide a link here.



Hi Abhay,

you, and someone else who is willing to help me out, can transfer a sample of the workbook i'm working in this link.

Hope someone can help! 
Tank you all.


----------



## Matt Allington (Dec 11, 2015)

Hey Bullstrik1.  Given you have a working solution for "all data", the best way to solve this (may be the best way regardless) is to create a calculated column in your calendar table that indicates the days that are to be included in your calculation (ie last 28 days).  Something like

=if(Calendar[date] >= today()-28,1,0)

This will give you a column showing 1 for the days to include.
Then create a new measure like this

Total Calls by period (last 28 days) = calculate([Total Calls by period),calendar[myLast28DaysColumn] = 1 )

If you want to learn to write DAX, you can check out my book here Learn to Write DAX - the book for all Excel users


----------



## Bullstrik1 (Dec 11, 2015)

Matt Allington said:


> Hey Bullstrik1.  Given you have a working solution for "all data", the best way to solve this (may be the best way regardless) is to create a calculated column in your calendar table that indicates the days that are to be included in your calculation (ie last 28 days).  Something like
> 
> =if(Calendar[date] >= today()-28,1,0)
> 
> ...



Hi there Matt, and tanks for your feedback! 
I belive your solution would sufice if i hadn't the need to supress national holidays and weekends for my calculations. That's where my real problem is: i must exclude this "special" days from my calculations.

Still lookng forward to read more opinions/solutions 
Tanks all.


----------



## Bullstrik1 (Dec 12, 2015)

Bullstrik1 said:


> Hi there Matt, and tanks for your feedback!
> I belive your solution would sufice if i hadn't the need to supress national holidays and weekends for my calculations. That's where my real problem is: i must exclude this "special" days from my calculations.
> 
> Still lookng forward to read more opinions/solutions
> Tanks all.



I just wanted to add that i already have a column, in my calendar table, that shows 0 for weekends and holydays and 1 for working days. I guess my problem is: how to filter my principal table, ImportDumps, based on the criteria - The Last 28th 1's :P


----------



## Matt Allington (Dec 12, 2015)

The solution is the same, the formula may be different. My advice is copy your calendar table (including the working days column to excel). Then write an excel formula that flags the last 28 working days. Once you have worked out the logic in excel (in which you are probably more familiar) then work out the DAX equivalent.  Post your excel formula back here if you are stuck.


----------



## Bullstrik1 (Dec 12, 2015)

Matt, tanks so much for your feedback!

Since i'm stubborn like a donkey (and dumb as well  ) , i tried once more DAX before trying your solution. Please don't get mad at me, i just had this one last idea.... it might work 


I created this measure: CallsByPeriodLast28WDays:=CALCULATE([Total Calls by Period];dDays[WorkDay]=1; DATESBETWEEN(ImportDumps[Date];LASTDATE(ImportDumps[Date])-28;LASTDATE(ImportDumps[Date]))), where [Total Calls By Period] is the measure i created to calculate the calls by period for all my data (including holidays and weekends). This new measure [CallsByPeriodLast28WDays] is almost correct, just misses 20k records out of 60k lol
Any idea what am i doing wrong? I know i'm almost there...!

Cheers


----------



## Bullstrik1 (Dec 12, 2015)

Ok, i know now what i am doing wrong, but don't know how to correct it in order to make it right!

The problem is that, when i created the new measure [CallsByPeriodLast28WDays], the first filter of the formula - dDays[WorkDay]=1 - is not filtering the principal table - ImportDumps - in order to make the weekend and holidays unavailable and so the formula LASTDATE(ImportDumps[Date])-28 is not working properly.

I have dDays table connected to ImportDumps table, as u can see in the screenshots above, but the filter was not made... I also tried to insert a calculated column with the 1's and 0's and make the filter by that column but its also not working. Anyone have an ideia what filter i could do in order to make this work?

Tanks all!


----------



## Matt Allington (Dec 12, 2015)

Here is a sample workbook that has a calculated column that flags if the date is in the last 28 working days.  
https://dl.dropboxusercontent.com/u/30711565/last 28 days.xlsx

Here is the DAX formula


```
=IF (
    SUM ( Calendar[Working Day] )
        - CALCULATE (
            SUM ( Calendar[Working Day] ),
            FILTER ( Calendar, Calendar[Date] <= EARLIER ( Calendar[Date] ) )
        )
        <= 28,
    1,
    0
)
```

You can then write a measure that uses your current working measure but filters for this column.


----------



## Bullstrik1 (Dec 10, 2015)

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 (Dec 13, 2015)

Matt Allington said:


> Here is a sample workbook that has a calculated column that flags if the date is in the last 28 working days.
> https://dl.dropboxusercontent.com/u/30711565/last 28 days.xlsx
> 
> Here is the DAX formula
> ...




Hi Matt! Tabjs so much for your feedback!
I tried your DAX forumla, but seems like i got a "naked" column reference, cause i got this message when i finished inserting the dax formula in the calculated column: "The value for column 'Day' in table 'dDays' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified." And the region selected was "EARLIER ( Calendar[Date] )".

Any ideas? 
Tanks for your help.


----------



## Matt Allington (Dec 13, 2015)

Well this formula was written for my test workbook. Did you change the column names to match your workbook?  Do you have all the columns, like working days?


----------



## Bullstrik1 (Dec 13, 2015)

Matt Allington said:


> Well this formula was written for my test workbook. Did you change the column names to match your workbook?  Do you have all the columns, like working days?



Yep! I did change the columns names. The transformed formula goes like this:


```
=IF(SUM(dDays[WorkDay])-CALCULATE(dDays[WorkDay]; FILTER(dDays; dDays[Day] <= EARLIER(dDays[Day])))<= 28;1;0)
```

My dDays table is my calendar table, dDays[Day] is the date columns in my calendar table and  dDays[WorkDay] is the column that flags a date as workday(1) or non-workday(o)

Cheers


----------



## Matt Allington (Dec 13, 2015)

Which table are you putting the calc column in?  It belongs in the calendar table.


----------



## Bullstrik1 (Dec 14, 2015)

Matt Allington said:


> Which table are you putting the calc column in?  It belongs in the calendar table.




Oops! My bad...I'm sorry, i was calculating that column on my data table. I will try it once i get home.

Tanks Matt!


----------



## Bullstrik1 (Dec 16, 2015)

Bullstrik1 said:


> Oops! My bad...I'm sorry, i was calculating that column on my data table. I will try it once i get home.
> 
> Tanks Matt!



Hi all!
I'm sorry for the late response.

Matt I tried your formula and it does work in someway, although i'm still not very happy with it.

Happens that my calendar table has more dates, beyond the most recent date with data in my fact data, and so your formula flags the last dates in my calendar and not the last dates in my fact data - say i only got calls data till 12-11-2015 and my calendar table got dates till late 2024.
Since i want to automate this model, i think my calendar dates must be way beyond my actual data dates in order to update the calendar file the least times i possibly can ( i could, for instance, add some VBA to update this calendar table, but i seriously wouldn't want to update this table/file every time I load data into this data model, since i'm afraid the file could grow big and performance goes downhill).

Kinda lost at this point, and don't really know what to do next 
If someone have some ideas and don't mind sharing with me, i would really apretiate.

Cheers all and tanks again for your feedback Matt!


----------



## Matt Allington (Dec 16, 2015)

Calendar over run can be a hassle.  Best to fix it at the source.  Read these 2 blog posts I wrote on the topic.

Regular calendar table Create a Custom Calendar in Power Query - PowerPivotPro

445 Calendar table  Create a 445 Calendar using Power Query - PowerPivotPro


----------



## Bullstrik1 (Dec 17, 2015)

Matt Allington said:


> Calendar over run can be a hassle.  Best to fix it at the source.  Read these 2 blog posts I wrote on the topic.
> 
> Regular calendar table Create a Custom Calendar in Power Query - PowerPivotPro
> 
> 445 Calendar table  Create a 445 Calendar using Power Query - PowerPivotPro



Heya!
Matt, this is freaking nice material mate!
 I am using powerquery to pull my data from several excel files located on a folder, how come i never thought about powerquery to genarate a calendar? :P

One question though:  any chance one being able to use this formula Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2015,1,1)) but with some expression that pulls last date with data from another query (already produced in powerquery area), instead of DateTime.FixedLocalNow() ? This would automate my process in no time and would alow me to use your DAX formulas above as soon i got this sorted out in data model.

Tanks alot for sharing this material Matt. Seems like i got alot to learn... A long way to go before i manage to do the stuff i want with powerbi :P

Cheers


----------



## Bullstrik1 (Dec 17, 2015)

Bullstrik1 said:


> Heya!
> Matt, this is freaking nice material mate!
> I am using powerquery to pull my data from several excel files located on a folder, how come i never thought about powerquery to genarate a calendar? :P
> 
> ...



After a full dayn of work (lol) i managed to get what i wanted. I will post the powerquery code as soon as i can.
Just need to build the datamodel now and use Matt's formula.

Lets see how it goes! 
Cheers


----------



## Matt Allington (Dec 17, 2015)

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


----------



## Bullstrik1 (Dec 10, 2015)

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 (Dec 18, 2015)

Matt Allington said:


> 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
> ...



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 (Dec 19, 2015)

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 (Dec 22, 2015)

ImkeF said:


> 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


----------

