Filter Last 12 Months Without Slicer

[Anonymous]

Hello

    I'm new in Power BI. I'm collecting data the from a SharePoint List and showing in a PBI. I'm trying to show only the last 12 month from the data collected. I'm able to do it with the filters, but I would like to make it "automatically". Basically, what I was considering is create an Index column and look from the last entry minus 11 months to show the last 12 including the las entry. I show the last 12, but when a new entry is made it does not remove the first month on the list and show 13 months now. The data is entered by a personnel, so it does not have a specific date of entering it. To calculate the last ID entered I use:

ID Last = CALCULATE(MAX('Work Order Back Logs (3)'[Index]), FILTER('Work Order Back Logs (3)', 'Work Order Back Logs (3)'[Index] = MAX('Work Order Back Logs (3)'[Index])))

     I calculate the Index number I want to show with: 

DateDiff = [ID Last]-11

 Attached is a screen shot from the data table

P.S. Sorry if I use the incorrect termns

[DOLEARY85 · Accepted solution]

Hi,

try this to flag all the dates in current 12 months with a 1 in power query:

if [#"Date Field"] <= DateTime.Date( DateTime.LocalNow() ) and [#"Date Field"] > Date.AddYears(DateTime.Date( DateTime.LocalNow() ),-1) then 1 else 0

then filter on only the 1's and that will only bring them through to the report view.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

Hello @DOLEARY85 , thanks for the fast response. I write down the code but it show me an error. When you say [DateField], you meand the last date column?

Thanks

[DOLEARY85]

Try changing your IF to lower case,

yeah that Date column should work.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

@DOLEARY85 Works great, thanks a lot for everything.