Calendar Based on Last Date

[dmutka]

I am trying to create a calendar in Power Bi that predicts a customers future service dates based on the last service date that we have recorded for them. I am able to create a a measure that shows the next pick up date, but what i am looking for is all of the service dates for the rest of the calendar year.

Below is my desired result:

Customer Name	Last Pickup Date	Next Pickup Date	Service Frequency
Customer A	5/1/2019	6/1/2019	Monthly
Customer A	6/1/2019	7/1/2019	Monthly

With 5/1/219 being the most recent service date we have recorded for this customer, I would need the rest of the dates to populate based off that and service frequency. Is something like this possible in Power Bi? 

Thanks for any ideas in advance!

[Ashish_Mathur]

Hi,

You have shared the desired outcome, not the raw data.  Please share that as well.

[dmutka]

@Ashish_Mathur Here is a link to Sample data. As mentioned, I am looking up the customers last service date, and then trying to create a new table based off the customer selected/service frequency, that will show predicted service dates in the future. Any help, if possible, would be appreciated.

Thanks!

Sample File

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[dmutka]

@Ashish_Mathur  I appreciate you taking the time to help me with my problem, but it is not what I am looking for. I am trying to take the future dates i.e. the next service dates and place them in same column as the last pick up date. So if I filtered the table to Customer A the table would populate like below:

Customer Name	Last Pickup Date	Next Pickup Date
Customer A	5/1/2019	6/1/2019
Customer A	6/1/2019	7/1/2019
Customer A	7/1/2019	8/1/2019

As mentioned, I know how to move a date a ceratin interval by creating a new column or measure, but i do not know how to then organize it into a similar manner as shown above.

Thanks!

[Ashish_Mathur]

Hi,

Will the service frequency of all customers be monthly?

[dmutka]

No, it can vary. Monthly, Every Other Month, Weekly, Every Other Week, Twice or Three Times Weekly.

[Ashish_Mathur]

So there should be another 2 table which should list down the Customer wise service frequency.  Please share all possible information for solving this problem.  Also, for the file that you shared above, please show the exact dates you are expecting for any 2-3 customers (who have different service frequencies).  If at all, i can solve the problem, I'd like to compare my answers with yours before i can share the solution file with you.

[dmutka]

Good Monring @Ashish_Mathur ,

I have updated the sample to include each service frequency as well as actual servie dates for each customer that has different service frequencies. Basically the service frequency is actually based on days. So if the customer is monthly, it is 28 days apart, Every Other Month is 56, etc etc. thanks again for all your help as this resolution will really hep me out.

Updated Sample File

[Ashish_Mathur]

Hi,

I am sorry but i dont think i can solve this.  Someone else will help you.

[MitchM]

Good morning @dmutka. I took a look at the file and one challenege will be for the thrice and twice weekly customers. For example, for customer A you have Sat - Wed - Fri (which can be extrapolated as Wed - Fri -Sat), and for Customer B you have Tue - Thur. Are the service days of the week consistant? If there is no pattern then you could use the average of days between visits (every 2.3 for thrice and 3.5 for twice) to populate a table.

---

@dmutka wrote:

Good Monring @Ashish_Mathur ,

I have updated the sample to include each service frequency as well as actual servie dates for each customer that has different service frequencies. Basically the service frequency is actually based on days. So if the customer is monthly, it is 28 days apart, Every Other Month is 56, etc etc. thanks again for all your help as this resolution will really hep me out.

Updated Sample File

---

[dmutka]

Hello @MitchM ,

the service dates are very consistent. if a customer is serviced Mon, wed, Fri, then they will be serviced those same days every week (excluding holdiays) which is an acception that I am not conerned about.

Thanks for any help that you can provide!

[Anonymous]

If you have measure1 that gets first date, measure2 that gets second and so on, you can use a calculated table using a set of UNION that collates all of your calculated tables for each measure.

Unfortunately measure returns a scalar value (so 1 value) but maybe also a CALCULATETABLE can help

[dmutka]

Thanks for the reply! I have been messing around with different things but i am unsure of how to accomplish my goal based on your feedback. It is primarily because I do not understand how CALCULATETABLE works fully. i have watched  few tutorials but I am still struggling. would you be able to lend a hand with sample DAX that you think would work? Thanks in advance!

---

@Anonymous wrote:
If you have measure1 that gets first date, measure2 that gets second and so on, you can use a calculated table using a set of UNION that collates all of your calculated tables for each measure.

Unfortunately measure returns a scalar value (so 1 value) but maybe also a CALCULATETABLE can help

---