Hi,
I'm trying to reconstruct a dashboard that I created using monthly exported .xls files. in the origional Dashboard I added a true/false formula to the source data to show weather the date was older than 365 days then in my pivot tables I could use the true/false column to show only transactions from the last 12 months.
The new Dashboard will be using data obtained direkt from an Oracle Power Query, the advantage to this system is that it will show me real time results instead of waiting till the end of the month for the Monthly data export as we do now.
unforntunately I haven't found a way to recreate the filter for the rolling last 12 months in Power Query or in Power Pivot.
Hoping you can help.
Andy
Hi Andy,
Power Query has a Date.IsInCurrentYear function that you can use to identify dates in the current period and then apply a filter to exclude that data, either by not importing it at all, or by returning a TRUE/FALSE that you can use to filter later on.
There is also a Date.IsInPreviousNMonths function which might suit you better.
Mynda
Hi Mynda,
thanks, the Date.IsInPreviousNMonths function produced exactl the right results that I was looking for although it did take me awhile to get my head how the the syntax works (me being a total M noob).
I'm sure I'll be using this and some of the other date fuctions I tried out this evening alot in future projects 🙂
Andy
Great. Glad you took the time to give it a go and in the process discovered some other functions 🙂 Most people give up too easily. This tenacity will serve you well.
