[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]

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.

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]

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]

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]

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

 

[Bullstrik1]

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]

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]

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]

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]

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

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]

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