# How to use Cube functions to perform Sumifs ( sum range , Start Date array, <=specific month end, End Date array, >=specific month end)



## Topher60657 (Feb 9, 2016)

Hello, 

I am trying to replicate standard excel functionality using cube functions.  I have employee transactional data.  This data contains a unique ID, an effective date, and an employee status - active or term.  The data is sorted by empl id and effective date, both ascending.  From this data I can add a column in Power Query with end date.  I also add a column that indicates if each row is associated with an Active (A) employee, 1 or Termed (T) employee, 0. 

So an excerpt of the data would look like:

Empl ID	EE Status	Start Date	    End Date	Headcount
1	        A	        1/16/2007     9/30/2007	1
1	        A	        10/1/2007	    10/1/2007	1
1	        A	        10/2/2007	    1/31/2009	1
1               T              2/1/2009       2/9/2016      0

This row of data says employee 1 was active from Jan 2007 to Sept 2007.  Something changed to his attributes, and he was active on 10/1/2007, then something else happened and he was active from 10/2/2007 to 1/31/2009.  The employee was termed on 2/1/2009 and his headcount is no longer counted, thus the 0.   

I load the power pivot data model with this data. 

I am a huge fan of the cube functions and I was wondering if it is possible to build the table below using the power pivot data.

My desired result would look like this:

Rows of month end dates.  In the values field, sum the Headcount column if the Start Date is <= the month end date AND the End Date >=month end date.  

Month End   Value
1/31/2007       1
2/28/2007       1
.....
1/31/2009       1
2/28/2009       0


The sumifs formula would be =sumifs([headcount],[Start Date],"<="&Month End,[End Date],">="&Month End) - can this be replicated using cube functions or utilizing any other functionality in excel, while maintaining the Power BI/Excel Online functionality?

Currently I have a pivot table loaded with calculated columns for each month end that performs the logic.  The problem with this approach is that I have a ton of columns and that isn't good form.  

Thanks so much for your help!


----------



## Matt Allington (Feb 9, 2016)

The problem with cube formulas is the list of dates doesn't grow when your data grows - pivot tables do grow. Putting that aside, just creat the pivot table and then convert it to cube formulas. OLAP TOOLS\CONVERT TO FORMULA


----------



## Topher60657 (Feb 9, 2016)

Thanks for taking a look at this question.  I have converted the pivot table to formulas but the challenge is, I current can't get my desired result using a pivot table.  The cubemember is a specific date and the cubevalue is based on a specific date in my table.  I am wondering if it is possible to write a cubevalue function that is based on date ranges or multiple cubemembers.... that function like the sumifs function?  I took a look at another post....could I create a new data table in power pivot with my date values in rows.  Next add a calculated column that would sum the headcount column in my employee data based on logic?

I appreciate this forum so much. 

Thanks!


----------



## SimonNU (Feb 9, 2016)

If it is possible then I'd probably suggest finding someone skilled in MDX.  

From what I can tell, however, you would be better off doing most of the work in DAX and referencing that with cube formulas if you _really_ want to.


----------



## Matt Allington (Feb 10, 2016)

Topher60657 said:


> I current can't get my desired result using a pivot table.



Oh, ok.  Before you said your pivot table that performs the logic, so I assumed it must be working in a pivot.  

I have no doubt you can do this in DAX without SUMIFS.  Once it is working in Power Pivot, you have a choice if you use cube formulas or a pivot table.

It is very difficult to help unless you post a sample workbook that contains realistic data, a pivot table and measures that you have written (some probably not working) and a simulated pivot showing the answer you are expecting.

So if you can post a sample, I will take a look.

PS: I don't think you need MDX.


----------

