Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Breaking time intervals into smaller intervals

Hi Experts,

 

I'm hoping to get your help with creating a payments schedule table based on a contracts table. The existing table is similar to this:

 

ContractValid fromValid toPay frequencyNotes
A10/1/20198/31/2020quartely11 mths
B9/1/20198/31/2020bi-annual12 mths
C10/1/201910/31/2020annual13 mths
D9/1/201911/30/2020one-time14 mths

 

I added the "Notes" column just to highlight that for some contracts the last payment is for a shorter time interval than the rest.

 

The desired outcome would be another table looking like this:

 

Pay IDPay fromPay to
A110/1/201912/31/2019
A21/1/20203/31/2020
A34/1/20206/30/2020
A47/1/20209/30/2020
B19/1/20192/29/2020
B23/1/20208/31/2020
C110/1/20199/30/2020
C210/1/202010/31/2020
D19/1/201911/30/2020

 

Your help will be much appreciated!

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous - Hmm, not sure there is a way to do that at scale. In DAX you could brute force it by using GENERATE between the to and from for each row, filter out what you don't need and then a UNION of everything. That would be OK if it was 4 rows but I doubt you really only have 4 rows. Maybe if you unpivot your from and to columns first. Will have to think about it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler  Thanks for looking into this. You are right about the brute force, the source table has a lot more than 4 rows and is often updated.