Replace Sumifs Function with Pivot Table in Power Pivot

[cbrown6305]

Hello,

I am working with a large data set that I have housed in the excel data model via a power query and am using power pivot to bring data into my workbook. The data has three columns of interest for me: service date, collection date, and payment. Formerly, when the data was housed in a standard table, I would just run a sumifs function on a monthly date column in order to retrieve all payments that were collected after month end for service dates prior to that month. The prior formula looked like this...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month End[/TD]
[TD]Monthly Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/31/17[/TD]
[TD]=sumifs(Table1[payment],Table1[collection date],">"&A2,Table1[service date],"<="&A2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/31/18[/TD]
[TD]=sumifs(Table1[payment],Table1[collection date],">"&A3,Table1[service date],"<="&A3)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/29/18[/TD]
[TD]=sumifs(Table1[payment],Table1[collection date],">"&A4,Table1[service date],"<="&A4)[/TD]
[/TR]
</tbody>[/TABLE]


Since I am now housing the data in the power pivot/data model, I can't just run a formula on the data set. I can only query the data set using a pivot table. I have considered creating a pivot table for the entire data set and running the above formula, but that would defeat the purpose of housing the data in the data model via power query. I'd much rather run a pivot table that functions just as the above calculation does. I think that the answer lies somewhere with DAX measures, but I can't quite figure it out.

Thanks for your help!

 

[pjmorris]

maybe I've missed the point, but if you have dates as your column headings then a measure defined as SUM(Table1[payment]) would automatically filter the data.

You might need to create a Calendar table and link the dates using the PowerPivot Manager. Its a whole new skill set and I've gone from being the office expert on traditional excel to a bit of numpty now that I'm trying to use the Power suite of tools - I'm persisting because I think its probably worth it. For info I'm reading two books: 'M is for (Data) Monkey' and 'Power Pivot and Power BI'; the first covers Powerquery which is really powerful at manipulating the source data, and the second is for Powerpivot.

 

[cbrown6305]

maybe I've missed the point, but if you have dates as your column headings then a measure defined as SUM(Table1[payment]) would automatically filter the data.

If I only have payment dates in my column headings, it will just sum the payments occurring on those dates. I need to sum the payments that fit into the following criterion:

For some date "x"
All payments have a payment date after "x" AND a service date on or before "x"

Ideally, "x" would be the service date in the row quadrant

You might need to create a Calendar table and link the dates using the PowerPivot Manager.

I'm not really sure what a calendar table would achieve. I would think that all I would need to use is the date and ">","<", and "=" arguments.

 

[pjmorris]

Hi, the calendar table has specific characteristics within PowerPivot. I don't have the competence (I'm also learning) to explain here (or the space), but if you look at the PowerPivot book I mentioned or other online powerpivot briefing material I believe it will answer your problem.

Sorry I cant help more.

Regards