topic Re: Creating Custom Date range in Desktop

Creating Custom Date range

sieed — Wed, 08 Mar 2017 16:12:29 GMT

I am trying to create a custom date range that shows the last fifteen days no matter if there is data for that date in my table or not. As long as it's in the last 15 days range, it should be shown on the graph. Here's my current graph:

As you can see, it is not showing the last 15 days because I don't have any data pripr to March 1st. However, I need to display the last 15 day. I tried creatin a calculated column called Last15Days = toda() - date. However, this only displays dates which are in my data. So, for instance, Feb29th is not displayed although its in the last 15 days range.

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 16:54:17 GMT

sieedwhy you deleted your previous thread. And you are posting this question as New (My Solution for that thread?)

Re: Creating Custom Date range

LaurentCouartou — Wed, 08 Mar 2017 17:01:52 GMT

I assume you do not have a dedicated Date table in your model. If not, add one and link it to your data. (Date tables are meant to solve this kind of problems).

The most basic date table has only one column and contains a set of contiguous dates.

The quickest way to create one would be to add a calculated table to your model and use the CALENDARAUTO() function.

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:08:34 GMT

I did not delete the thread. I got a message saying that my post is under review and it's been an hour since I got that message. That's why I created a new one. I di dnot even se eyour reply. Can you please post it again?

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:39:21 GMT

LaurentCouartouSo, do I have to jon this date table with my current table? How exactly does that work?

I have already created the datwe table, but am not sure hwo tojoin it with the job table I have because there is no common field. I can share my pbix file but I am not sure how to attach it.

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 17:40:18 GMT

oh. I thought, you have deleted the thread.

Try this:

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:49:25 GMT

I tried that but dont' you think that would display all the dates without data as opposed the last fifteen dates?

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:51:25 GMT

LaurentCouartou, I have created a Date Table with the past fifteen dates. Question is, how do I join it with my current table? There isno common field between the two... nI can share my pbix file but I dont know how to attach it.

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 17:51:35 GMT

See, you can see all the 15 instances displaying (Blank Portion highlighted). I am newbie too, so that's what i got it in this situation.

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:52:52 GMT

yes but if I have a huge dataset, it would display every blank date as oppoed to the last fifteen dates.

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 17:53:10 GMT

date is the common field in both tables. you can create link.

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 17:55:12 GMT

I don't think, it will display all the data as in the Date table, we have created the fields based on today -15 days, so, the date table limits data upto previous 15 days.

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:55:15 GMT

rocky09, the Date table contains a bunch of dates. There is no JobID. So, can't connect it.

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 17:59:11 GMT

rocky09, did you create those fields in the job table? Leme show you how my data looks like:

Re: Creating Custom Date range

rocky09 — Wed, 08 Mar 2017 17:57:55 GMT

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 18:01:30 GMT

Re: Creating Custom Date range

sieed — Wed, 08 Mar 2017 18:27:35 GMT

rocky09, sorry I had to just drag the line from the Job table to the date table and it worked. Thank you.