I have been getting this question a few times in the last week, e.g. in my ask a questions page here, on how we can get the number of active products between two time intervals in Excel. Lets answer the question on my ask a questions page in todays blog post.
The question was:
DESIRED OUTPUT: Pivot table showing count of asset available by Month; Column Headings = Months & Year; Row Headings = Class.
TABLE1: Includes a list of assets and corresponding Class, Acquired date, and Disposed date.
Asset,Class,Acquired,Disposed
A,TEL,2/16/2010,10/06/2011
B,LBH,3/20/2010,05/23/2012
C,RBH,4/11/2010,06/22/2011
D,TRJ,1/17/2011,09/05/2012
E,DGH,5/9/2011,05/02/2012
F,DGH,7/18/2011,03/13/2012
G,RBH,9/23/2011,01/21/2012
H,TRJ,5/28/2012,06/07/2012
I,THG,7/2/2012,10/11/2012
J,TBD,8/6/2012,12/02/2012TABLE2: Date Table including fields for Date, Month, and Year
EXAMPLE OUTPUT: Based on the data above, the pivot table output for June-August of 2011 would be as follows…
Year: 2011
Month: Jun, Jul, Aug
DGH, 1.0, 2.0, 2.0
LBH, 1.0, 1.0, 1.0
RBH, 1.0, 0.0, 0.0
TBD, 0.0, 0.0, 0.0
TEL, 1.0, 1.0, 1.0
THG, 0.0, 0.0, 0.0
TRJ, 1.0, 1.0, 1.0
Ok lets solve this using DAX.
First I load the table into Excel 2013, make a table out of it and push it to the Excel data model.
Observe that I changed the dates to have all dates be in 2011 to make it easier for us to debug.
Next I created a date table in Excel:
Back in Excel I create the pivottable like requested in the question:
First things first, I would like to make sure to sort the months in its natural order. We can do that using the PowerPivot add-in. I created a calculated column that determines the month number:
=MONTH([Date])
I then hide the column and use the sort by other column button to tell the Data model to sort Month by Monthnumber
This results in the months sorted “right” in the pivottable:
Now back to the real problem. Notice that I have NOT created a relationship between the two tables.
First thing that I want to do is create a measure that counts the number of facts
Nr facts:=COUNTROWS(Facts)
In the result below this does the dates are not taken into account, but its takes care of counting the facts for the relationships that are there, like nr of products per class:
We are going to use DAX for that as we don’t have a relationship type that allows us to do a “between” function. The DAX function I created will look at each row from the fact table for each product where the product is active in the current month (acquired before or in this month and disposed after this month) based on the current row and column in Pivottable.
The DAX I wrote to do is the following:
Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])))
What does this function do ? First it will execute this function in each and every cell of this pivottable, lets take the first cell:
The values in this cell will be filtered to Fact[class] = “DGH” and Date[Month] =”Jan” based on the values on rows and column for this cell, the “Fact” table that will thus be filtered by Fact[class]:
Hence you can see the result of the measure “Nr facts:=COUNTROWS(Facts)” to be 2 in our pivottable image a few images up.
The date table is filtered by the Date[Month] column that is on rows:
That does not change the value of the measure in our pivottable as there are no relationships between “Date” and “Fact”
Now to make sure that we can also filter the Fact table by the dates we extend our DAX formula to do the following: Calculate the “NR Facts measure” where the “Fact” table (already automatically filtered by Class) has Facts[Acquired] before the current selected Dates[Date] or Facts[Disposed] after the current selected Dates[Date]:
Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])))
Because the Date table is filtered by the month that we have on columns we get a single date for each cell that we can use in our filter (as each month only has one date in our example). Just to be sure the formula also works when more then one date is selected I use MAX and MIN instead of values, that way we can easily switch to different granularities if we want to later.
There is one flaw of this approach. The Dates[Date] column right now is the beginning of the month, that will never give results for the entire month. So I added a calculated column to the Date table that give me the end date of the current month:
=EOMONTH([Date],0)
Now to use it in the function
Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[EoMonthDate])
&& Facts[Disposed] >= MIN(Dates[Date])))
This will give me the number of ”Facts” grouped by Month and Class:
I can now swap month for year and still get the right results (because of using MAX / MIN instead of values, thanks Rob Collie for setting this trend )
Or even add Asset on Rows instead of class and get the right results:
Hope this gave you a little more insight into DAX. You can download the Excel 2013 workbook here: https://skydrive.live.com/redir?resid=7F4E0559CC74581A!1088
Good read, thanks for sharing.
Was wondering whether 2 filters would have been faster than the formula above (as we are working on 2 smaller tables compared to one big table), something like shown below
CALCULATE([Nr facts],FILTER(values(Facts[Acquired]),Facts[Acquired] <= MAX(Dates[EoMonthDate])),FILTER(values(Facts[Disposed]),Facts[Disposed] >= MIN(Dates[Date])))
Hi Jason,
Yes the query above can give you better performance on large (wide) tables, especially because the filter operation doesn’t have to take all the other columns in the fact table into account but just the two columns you want.
Thanks,
Kasper
Thanks for the post! After reading Rob’s book, the disconnected table makes for a great pattern to remember.
Excellent Info! Is there an easy way to calculate the average age of the items (either separately or together) for the months that they are active?
Hi @Matt , This should be possible using wrapping the function with an AverageX
Thank you for all the excellent information. I have an issue that I have been fighting with for a few days and would like to ask if its possible using the data columns provided in Table 1 of the original post.
I would like to create a Pivot Chart that would shows, either via a stacked column or line chart with a horizontal axis of months (i.e. January, February etc…), the total number of assets acquired and disposed within a give month.
Its nice approach, but how to fix the totals to show valid values when you want to show the state at particular dates? Like I need to show state on the last day of y2011 and y2012.
Hi Mike, you can use functions like LastDate to get the the values from a specific date.
Hi Kasper
Thanks for this example, it is terrifically powerful.
Is there a way that you can use the disconnected tables in Power Map? I want to use the time dimension for a scene, but it is not enabled.
Thanks
Hi thanks for the above – however is it possible to calculate on a daily basis and then sum up into months, years etc. on the pivot table? – i.e. the total active days within the month? I can get the daily amounts but do seem to be able to group these days to get a total for the month.
Hi
You have a very interesting solution. I wonder if you could explain how to do if the disposed date is empty?
Regards
Robin
Hi and thanks for your instruction but I have a question. Why when I create relationship between date column (any of them) in fact table with date table, the result will be wrong because it will calculate and show the result in pivot table only in the month that it has relationship.
Is there another method to do a relationship and still get the correct result?
In my real data, we have close date of the contact(the date that we get contract signed), project start month(start of the project) and contract expire and also value of the contract. So basically, I have to do relationship between date column in calendar table with close date column in the fact table because I have to do a monthly report of sales (measuring sales performance by value of contract signed each month). Another report is pipeline report (allocate value to each month equally) so without relation I cannot do the first report and with relationship I cannot do second report. I know that I can duplicate the database but I think it might have another approach.
Thanks in advance
I tried that but its not working for me. My requirements are :-
I have a Project fact table which has a start date and latest activity date. One of the questions I am trying to answer is for each year how many active projects do i have?
So lets says ProjectA started in 2012 and is active in 2016. ProjectB started in 2011 and last activity was 2015.
So, the number of active projects by year needs to be shown as
2011 – 1 2012 – 2 2013 – 2 2014 – 2 2015 – 2 2016 – 1
I am confused how can i come up with the design or MDX and DAX to have this calculation done.
I have not run into a situation like this before so i am confused how to handle it.
I would really appreciate if someone can guide me. If any more detail is needed please let me know.
Excellent! Its just what I was looking for. Thank you a lot.
Best Regards