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]

It's 67.2MB, don't think it would upload

 

[scottsen]

Should be fine for dropbox / google drive

 

[BlackrazorNZ]

Have PM'd you a Dropbox link - file contains direct business data so not really wanting to post it publically.

 

[scottsen]

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

I think is your only problem. Your chart is trying to use dates from your data/fact table, which is not what you meant. Use fields from your calendar table (Date Range in your case).

 

[BlackrazorNZ]

I'm not sure I understand the difference sorry?

 

[scottsen]

You are using ExpQtr and ExpYear from your combined-data-file table. You need to use dates out of your Date/Calendar table (called "Date Range" in your model), or things will not work.

This will be a common theme. All DAX methods that do time intelligence (DATESINPERIOD, DATESYTD, SAMEPERIODLASTYEAR, etc) will work against your calendar table. Get used to it

While I am thinking about it though -- your calendar table has dates WAAAAY off into the future, will WILL have negative performance implications. You might want to trim that.

 

[BlackrazorNZ]

OK cool, working much better now, really appreciate your help

Last question : How do I hide the spike at the end that appears to be deriving its data set from blank cells?

I can do it with a slicer to cut out blanks, but that looks a bit ugly.

 

[scottsen]

So, you have a relationship between ExpPayment and your Date Range table... but there is a value in ExpPayment that isn't in your Date Range table... blank. So, that is causing some weirdness.

I generally try to start from pretty "base" measures:

So, I would have something like
TotalExpectedPayments := COUNT('Combined-Data-File'[ExpectedRepayment])
Then, when I ran into the problem you are seeing, I would make it a bit more complicated, but ALL measures that used [TotalExpectedPayments] would gain the benefit of changing in this 1 place.

It would become:
=CALCULATE(COUNT('Combined-Data-File'[ExpectedRepayment]), FILTER('Date Range', 'Date Range'[Day] <> BLANK()))

Which would filter out those rows...

 

[BlackrazorNZ]

Awesome, starting to make some headway now, you've been a heap of help.

I've now imported a much bigger data set, about 100,000 lines. The calculated residual works as a value for summing the total $ outstanding, and I can break it down by branch as expected. But when I try to add a slicer to the graph version using the measure you've helped me with, for some reason it only shows 1 branch in the data set?

As per :

vs (note the last slicer)