Data based on the last but one date in a table (but the last but one date is irregular)

[ericadyson]

Hi there.. I'm stuck!

  I want to know if the  LAST DPS (dividend per share) is different to the PREVIOUS one. BUT, the dividends are not announced evenly (some monthly, some 3 months, others different dates). (so the PREVIOUS date could be anything, last month, last quarter,  6 months ago etc)

I have a calculate field in the DATE table, which marks the current month as 0, the previous month as 1 etc.  BUT here's where I'm stuck. I can get the PREVIOUS dividend per share, when the field in the DATE table =1 and that works fine when the dividends per share are issued monthly. But how can I get the previous DPS for all stocks irrespective if it was monthly, quarterly or whatever.

I have tried OR.. couldn't get it to work, tried multiple FILTERS but can't do that on the same field.. I've tried all sorts. I AM STUCK!

Might anyone be able to help? I'd be ever so grateful if anyone could. Thanks Erica

[v-evelk]

Hi,

I am not sure that completely understood your issue.

However, I guess, you can try to filter your data by date parts if you set "Date Hierarchy" mode for your date bucket.

Also, I suppose, you can try to implement date hierarchy fields in your model.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com