[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

 

[Abhay Gadiya]

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]

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]

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]

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

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]

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]

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]

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]

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]

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.