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

[Topher60657]

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]

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]

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]

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]

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.