Future Dates based on a variable frequency

DAX DAX Calculations
1

Hi

I wonder if you can point me in the right direction. I can’t seem to find anything akin to what I am trying to do.

I have a dividends table. I know the payment frequency for each stock. The payment frequency in listed for each stock ie 1, 3 6 or every 12 months. Listed in the tickers table.

I have the date dividends has been paid but I want to create a visual that would highlight if a payment has been missed.

  1. How do I create the due date? Due Date would equal dividend date + month frequency for each ticker.
  2. If I had that, I could calculate where the latest due date showing is <=my selected date.

BUT how can I create this variable Due Date??? Can’t get my head around it. Any pointers would be greatly appreciated!

Model

date table
tickers table
dividends table.

Dividends table (one of my fact tables) is linked to date and tickers tables. 1 to many

2

Hi @Ericadyson

I hope you’re doing well. Please find the sample solution attached.

Please let me know if it doesn’t solve your issue. I’ll be happy to help

I’ve just used date function

Due date.pbix (64.2 KB)

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

1 Like
3

Dear @kkrj.ankit,
I can’t thank you enough. I knew it would be obvious but it wasn’t obvious to me. I had tried all sorts. I am so very very grateful. You['ve helped me no end. And thanks too for sending me the .pbix file. It’s so much easier to understand than words! Many many many thanks for your time and help.

I can now get on with the measure!
Yours, Erica

1 Like
4

Here’s an update. It might help others.
The date function didn’t work so well because the dividend frequency lies in the Tickers table and using the Date function I didn’t manage to add it to the Dividends table. But I did succeed via Power Query. This is what I did:

  1. Added the Frequency field from the Tickers table via Merged Query.
  2. Created the Due Date using M Add.Months now that I had the Frequency in the same table.
  3. Then I could create the measures I needed to track if a dividend was due or not.

So thanks for your help. It set me thinking afresh, and lo and behold, I have what I need and it wasn’t comoplicated at all. I just couldn’t get my head around it at first.

1 Like
5

Hi. Thanks for reaching out. No it isn’t solved. I tried Brian’s suggestion but to no avail.