calculation that sums 1 for each month

[afall_ant]

I am using direct query to connect to my dimension and fact table.

I have 1 fact table and 2 dimension tables and need a way to sum (1) in each column for each month between a beginning and end date contained in the fact table.

This is an example of the fact table:

[cust key] [plan key] [beginning date key] [ending date key]

123           10            20160701                   20161231

456           10            20140101                   20991231

789           15            20160101                   20160630

012           15            20150715                   20171231

345           20            20160315                   20160930

This is an example of what I need from the fact table:

[cust] [plan] [jan 16] [feb 16] [mar 16] [apr 16] [may 16] [jun 16] [jul 16] [aug 16] [sep 16] [oct 16] [nov 16] [dec 16] [total]

123    10                                                                                         1          1            1            1           1            1            6

456    10      1            1           1            1            1             1           1          1            1            1           1            1            12

789    15                                                                            1                                                                                            1

012    15      1            1           1            1            1             1           1          1            1            1           1            1            12

345    20                                 1            1            1             1           1          1            1                                                     7

                    2            2           3            3            3             4           4          4            4            3           3            3            38

I need the report to show a one in each month even though I don't have a count in each month plus it needs to be able to calculate under all critera pulls.

Thank you in advance for any help on this. 

[v-ljerr-msft]

@afall_ant

According to your description I have made a sample for your reference.

I assume you have tables like below.

Fact Table

Date Table

Firstly, use CROSSJOIN to create a calculate table called TestTable.

TestTable = CROSSJOIN('Fact','Date') 

Then use the formula below to create a calculate column Sum(1) for TestTable.

Sum(1) = 
IF (
    YEAR ( TestTable[Date] ) >= YEAR ( TestTable[beginning date key] )
        && YEAR ( TestTable[Date] ) <= YEAR ( TestTable[ending date key] )
        && MONTH ( TestTable[Date] ) >= MONTH ( TestTable[beginning date key] )
        && MONTH ( TestTable[Date] ) <= MONTH ( TestTable[ending date key] ),
    1
)

Last, use TestTable to create the Matrix Table in report.

Regards

[afall_ant]

Hi JerryLi, I can't really use this solution the problem is the cartesian product created results in 1+ Trillion rows being generated and I run out of memory.

The answer would work if it was a smaller set of data however, customers go back years and some renew plans each year plus other cancel and then come back.  The row set goes until 2099 as you saw so even using months this solution does not run.

Do you have any other suggestions?

Thank you in advance.

[ankitpatira]

@afall_ant Below are the steps you need to follow.

1. In your fact table, create calculated columns with start and end date and convert it to type number.

2. Either import date dimension (if you already have one) or create it via power query (one such example is here). In your date dimension have start and end date of each month with calculated column similar to step 1. Also have another calculated column which has month year in format of what you require (Jan-16). 

3. Create relationship between calculated column key of date dimension and calculated column key of fact table.

4. Use matrix visual with fields from your fact table and use column you created (Jan-16) from date dimension under Column areas of the matrix visual.

[afall_ant]

Hi ankitpatira, I am using a 1 in my fact table however, the reason I'm asking this is so not to build a cartesian product.  My table is a monster fact table with millions of customers and of course I'm not just building the plan and customer's only there are many other attributes as well.  What I am trying to figure out is can we just calculate the values in the columns for only the date period spread of each customer at runtime? 

I have a date dimension as well as a relationship between the fact and the dimension table. 

I'm also not sure what you are saying of column name created these are not columns names I created they are from the Dimension table.  So what I'm doing is allowing a user to select a beginning and end date and producing a report that only shows the period that they select.  They true active period could be years. 

Please let me know if this makes sense to you.

[afall_ant]

I am using direct query in this execrise.

I have a fact table and within the fact table I have a beginning date and and ending date plus a customer and a plan.  My dimension table is by month.  What I need is a way to get 1 in each month column for each customer no mater what the beginning and end dates are.  Here is an example of what my fact table looks like:

Fact table

[Customer key]    [Beginning Date key]   [Ending Date key], [PlanKey]

123                       20140105                     20991231              10

456                       20160701                     20161231              10

789                       20151215                     20160630              15

012                       20160101                     20160730              15

345                       20160315                     20160930              20

My Dimension table for plan is just a key an ID and a plan name.

I need to be able to create a measure that will have 1 in each month that I search for so if I look for 1.1.2016 - 12.31.2016 it should appear like this.

[Plan] [Cust] [Jan 16] [Feb 16] [Mar 16] [Apr 16] [May 16] [Jun 16] [July 16] [Aug 16] [Sep 16] [Oct 16] [Nov 16] [Dec 16] [Total]

10      123     1           1            1             1            1             1            1            1             1            1            1            1             12

10      456                                                                                          1            1             1            1            1            1             6

15      789     1           1            1             1            1             1                                                                                                  6

15      012     1           1            1             1            1             1                                                                                                  6

20      345                                1             1            1             1            1            1             1                                                       7

                     3           3            4             4            4             4            3            3             3            2            2             2            37

So if my filter date is less or greater than a year I still want the calculation to work.

Please let me know how I can do this thank you in advance?