Forum Discussion
Advocate IINumber of Months between two dates
Advocate IIMany thanks for this. Especially 'cos I managed to post it in the wrong part of the forum!
I think I'm going to fail with this anyway. What I was trying to do was to add a row for every month between two dates. I am able to do that and it works fine on my test data so I was working on the rest which included working out what a monthly figure for budget would be, which is why I needed the count above.
I tried something using Group to get a table with a count of the number of rows added using the above and that worked so I kept going. Alas when I replaced the test data with the final data all went Pete Tong. The query fails with a Stack Overflow error presumably because the table it is trying to create is just too big. It does this just adding the rows before I get to the bit where I need the count of months. There are 173 records in the table but honestly I did not think this would be too much as I have previously done this natively in a spreadsheet.
If I can fix the row adding I will try again but using your suggestions rather than generating another table which could be sucking up memory.
Thanks again.
Community Championtrevb - No problem, if you can post some sample, sanitized data and explain what you are ultimately trying to achieve (end result I am still not clear on) then we might be able to come up with a solution. 173 records should be trivial for Power BI to ingest and process. Some problems are trivial in DAX, like Months between dates and we might be able to get to a solution easier doing the necessary data manipulation in DAX and Power BI modeling rather than purely in "M".
Just as an aside to this, I would recommend anyone using Power BI as a serious tool to get hold of this book:
It is certainly not an easy read if you're very new to DAX or Excel formulas, but it has a wealth of information and will answer pretty much every question you might have.
- trevb
Ta Paul
I already have that but thanks for the tip. Sadly this time I needed to be using Power Query just because I am using the information in both PowerBI and Excel and that was the easiest way to bring them together.
