PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Hi

I have data which has 2 columns of dates (a From and To date), I then use an excel function to calculate the number of days there are in each month for the financial year in that date range. The function I use being:
MAX(0,MIN(([@rateTo]-1),40299+31-DAY(40299+31))-MAX([@rateFrom],40299)+1 - where 40299 is the date value for the particular month.

However I cannont seem to do a similar function in Powerpivot with DAX and I am currently importing the data into an excel table, applying the excel formulae and then linking the table in Powerpivot in order to create relationships with other data tables and produce more informative MI.

I also use the excel funtion below to count the number of a specific weekday between the from and to date:
=SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT([@rateFrom] & ":" & [@rateTo]))),{1},0))*ISNA(MATCH(ROW(INDIRECT([@rateFrom] & ":" & [@rateTo])),0)))
I am sure there must be a solution using DAX but I am very new to it, can anyone help?

In general, if you want to work with date in PowerPivot you have to define a Dates table which contains, for each date, a number of column with data related to each day (for example, a sequential number identifying the number of working days elapsed since a particular reference date). If you have two column dates (like from and to) you have to import the same table twice, relate each Date table to one of these columns and then you can create the DAX formula in the main table by using the RELATED function get the number you need for each date (like the number of working days to calculate elapsed working days between two dates).
In other words, you have to shift your approach from a pure-formula based approach to a mixed model-driven plus DAX-formula approach. This was not so comfortable in Excel, but it is much easier (and powerful) using PowerPivot.

BTW: I wrote a whole chapter about dates calculation in the upcoming book "Microsoft PowerPivot for Excel 2010: Give Your Data Meaning"
Look at chapter 7 in Rough Cuts available here:
my.safaribooksonline.com/9780735650947
You should find some examples very similar to your needs.


Marco

I answered by maybe there was some issue.
Take a look at chapter 7 of my book, available in Rough Cuts here:
my.safaribooksonline.com/9780735650947

You have to use Dates tables and shift to an approach more model-driven, instead of relying just on formulas.

Marco

Thanks Marco, but I'm still struggling to get the calculation I need.
By using the date table approach I can summarise the data based on the From or To date, but what I need is the values for each date between the from and To Date.
For example I have a row with:
From Date, To Date, Daily Rate.
10/06/10, 5/07/10,£100,

I would therefore want a count of 20 or £2000 for June and 4 or £400 for July (the end date is not included)

Uhm, so you might want to create a Dates table with no relationship with the fact table and a measure that makes the calculation for each row - I have a question for you, how many rows do you have? I'm worried for performance with your model. Usually, such a tables are splitted by the ETL in daily values that can be easily aggregated. Maybe PowerPivot will be faster too, but first of all I need to understand better the model you are building.

Marco