Graphing decreasing COUNT as dates approach end date

[BlackrazorNZ]

Hi all,

I have a table containing a large number of items which have an expected date of full repayment as a calculated field.

I'm trying to work out a measure for a chart, that shows a value for a COUNT of all the fields between the date itself and the final date.

So if the there are 100 items, first of which is repaid on 1st March 2015, and the last of which is repaid on 1st June 2018, then :

• The graph would show a count of 100 items at the 1st March 2015 point, and;
• Would show 1 item on 1st June 2018, and;
• Would show a decreasing scale between the two as the count between any given date and the last date decreases.

Example of what I want the graph to look like (excuse my amazing MS Paint skills) :

And here's what the graph looks like with a COUNT function (rather than a rolling decreasing SUM of COUNT which is what I want to try and achieve).

Thanks for any help

 

[BlackrazorNZ]

Secondary question : How do you group by date clusters (month, quarter, half year, year etc) in PowerPivot Charts? Was a built in function in basic PivotCharts...

 

[scottsen]

Love the question and mad Paint Skillz


Pretty much anytime you are dealing with dates, you are going to have a separate Calendar/Dates table. That is where you will define months, quarters, weeks, etc.
Understand and create date tables in Power Pivot in Excel 2013 - Excel


Your primary question is an interesting reversal of the typical YTD formulas. I'm not at my machine right now to test, but I *think* you will end up with something like:

=CALCULATE([NumPayments], FILTER(ALL(Calendar), Calendar[DateKey] >= Payments[DateKey]))


Where [NumPayments] is however you do your COUNT() and Calendar is your hot new calendar table, with a relationship back to your Payments table.


When that doesn't work, let me know... and I will figure it out when I'm at my computer

 

[BlackrazorNZ]

Hey, thanks for the reply.

Have tried inputting the formula you suggested, and it gives me values, but they're not anywhere near as high as expected. For example, there are 90+ separate date values in the column i'm counting, and 5000-ish records in total, but the highest value returned is 13?

Also, oddly, it works perfectly as a calculated formula in PowerPivot itself, but breaks when I try and add it as a Measure to a PivotChart.

 

[BlackrazorNZ]

For clarity, what I'm after (conceptually) is :

Area graph, where each value is equal to : the count of 'all rows where the date in field X is at least equal to field X of this row'.

Each row is a record of 1 item. A single item only has one date but one date can have many items.

For each output value I want a simple number, and it should decline as dates proceed forward.

 

[scottsen]

Sorry for the delay. Which ever of these should do what you want:

=CALCULATE([NumPayments], DATESBETWEEN(Calendar[DateKey], MIN(Calendar[DateKey]), LASTDATE(ALL(Calendar[DateKey]))))
=CALCULATE([NumPayments], FILTER(ALL(Calendar), Calendar[DateKey] >= MIN(Calendar[DateKey])))

Btw, this assumes that the date field you put on your chart comes from the Calendar table as well.

 

[BlackrazorNZ]

Thanks for that. The actual formula I put in is :

=CALCULATE([Barcodes], DATESBETWEEN('Date Range'[Date],MIN('Date Range'[Date]), LASTDATE(ALL('Date Range'[Date]))))

... which doesn't work and instead produces the following error :
"The value for 'Barcodes' cannot be determined. Either 'Barcodes' doesn't exist, or there is no current row for a column named 'RowCount'."

If I change the [Barcodes] to COUNT[Barcodes] like so :

=CALCULATE(COUNT([Barcodes]), DATESBETWEEN('Date Range'[Date],MIN('Date Range'[Date]), LASTDATE(ALL('Date Range'[Date]))))

... the formula works and produces a lovely graph, but it doesn't show it as a declining total :

So close and yet so far! Any idea what's preventing it from tallying the way your formula suggests it should?

 

[scottsen]

Hmmm.... My [NumPayments] was just a measure := COUNTROWS(Payments), but using COUNT(Payments[Barcode]) should give the same results (I'm calling the TABLE, "payments").

The quarters in your horizontal axis... its from the 'Date Range' table?

 

[BlackrazorNZ]

Yes. Indicative outline is below, this extends all the way to 2150.

[TABLE="width: 331"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Quarter[/TD]
[/TR]
[TR]
[TD="align: right"]26/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]27/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]28/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]29/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]30/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]31/05/2014[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Possible to share your workbook?