Hey everyone. I'm trying to create an aggregate table to take advantage of the incremental refresh using 'power query'. I normally create this using sql, but unfortunately that process takes away the ability to do the incremental refresh...so I'm stuck having to use the editor.
I have an aggregate table setup....now I just need to 'define my sale' so I can have an accurate sales count. Below is the definition:
Count of distinct contracts
Status in "A", "C"
Accounting Date not null
Cancel Date is null or Cancel month is > Accounting month (this means we don't count a sale if the cancel happens in the same month as the accounting date)
I can get most of this logic to work except for the last statement. I can't use 'datediff' because I need this function to read from the aggregate table and I can't use datediff in a direct query table. I'm feeling stuck..so any help in the right direction would be greatly appreciated.