Hi @Maisy.
Sorry to take so long to get back to you. Canada Day and that pesky day job conspired to delay me until now.
#2(a) No, you need to have an active relationship with a date table that marked as such in order for the Power BI time intelligence DAX measures to work properly.
#2(b) I don’t think there’s any issue per se with the Moving Average pattern, it just can give wierd results when there’s not a full period (in your case, 12 months) of historical data before each date. A quick comparison with Excel produces slightly different numbers, and perhaps others have some experience as to why.
I’m not sure to which [MW u/Warr] row you are referring to when you say the value should be 5465: do you mean the value on the “Total” line (6093)? If so, this sounds like a case of the “incorrect total” issue that arises when you take a display the simple total of a measure that uses an iterative, or “X” function. A quick search of the eDNA site returned the following links which explain this situation more fully.
If you still need help after that, please post a PBIX, a sample dataset in Excel, and a screen shot of the expected outcome in Excel and I’ll dive in more deeply.
Hope this helps.
Greg