Date parameter in PowerPivot

Asked Apr 04 '16 at 23:52

Active Apr 04 '16 at 23:52

Viewed 330 times

I'm trying to figure out if it's possible to do this in powerpivot for Excel.

In my SQL server datasource, I have a table that has a payment date field and an Amount field.

Now I want to have 2 calculated fields: Pending amount and Paid Amount.

I want to user to select a date. My idea is that for example he puts a date in cell A1 in the Excel sheet, and then this date is used for the calculated fields.

So for example If in my datasource I have a row with Amount 1000€ and date 31st December 2015, and the user puts value 1st November 2015 in cell A1, the Pending amount should be 1000€ and Paid Amount should be 0.

If the user changes the A1 cell to 15 January 2016 for example, then Pending Amount should be updated to 0 and Paid Amount should be 1000.

Is it possible to achieve this with cell A1 and some VBA code to update the powerpivot data? Or maybe I have to use some other tool so the user can select a date?.

Also I would like to know the DAX formula for the calculated fields in order for this to work.

asked Apr 04 '16 at 23:52

Jack Casas

Why don't you filter the pivot table to show only the date you want to look at? – teylyn Apr 04 '16 at 23:58
How will filtering the pivot table affect my calculated fields? – Jack Casas Apr 05 '16 at 16:14
Create a measure. Create a pivot table. Filter the pivot table. – teylyn Apr 05 '16 at 20:37

Date parameter in PowerPivot

0 Answers0