PDA

View Full Version : Need DAX formula for PowerPivot



saifu786
2012-12-28, 11:48 AM
Hi All,

I am new to this forum.
Need a small help regarding building DAX formula for PowerPivot.
I have two tables in one sheet in excel. One is City and State data with Promotion Start Date and End Date.
The second one is just a list of days with date, month name and year for 2009 and 2010.

I want to match first table with second to create a Power Pivot which should be as follows:

1. Row Heading should contain State from first table
2. Column Heading should contain Year and month from second table.
3. Values should be populated with count of state against each month.

I have prepared a sample output table manually. Please refer to screenshots below for both input tables and desired output sheet.

994 995

I know and can achieve row heading and column heading sections easily but unable to build a DAX measure to populate value section.
Request you to please help build this formula. Looking for a quick response and would appreciate your effort on this.

Note: Also attaching the excel file for your reference that am working on.

Thanks in advance
Md Sarfaraz

Herbds7
2013-05-14, 05:27 AM
Excel 2010 with free PowerPivot Add-In
http://58918aa17d4002474361-7ac2b7ee2bc9d74042ff2920135ae995.r25.cf1.rackcdn.com/05_07_13a.xlsx
If you get *.zip, don't unzip, just rename *.xlsx