Need to calculate the last 6 months every month

[Jerryvb]

Hi,

So I am running into troubles create a report. I need to report the last 6 months but then each month. For example:

Right now I need month 2015 december until 2016 May. Next month I need 2016 January till 2016 June.

I need this report monthly so the tickboxes are not going to do much for me unfortunately. I tried doing this with syntaxes but I have no clue how to do this @ a lot of errors.

Posting date has the date I need. This is connected to a field called Material within the same table(table 1). Material is connected to Material# in (master) table 2. Then I have table3 which has Material too and is connected to (master) table 2 as well.

Hope this helps and someone is actually willing to help this noob :).

Thanks!

P.S: Cannot change the source files. Hence I need it per month.

[Sean]

@Jerryvb You'll need a Calendar Table for this. Do you have one?

If not create one - follow this example - http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

Then it will be pretty straightforward...

This is your Running Total for the Trailing 6 Months ONLY

RT Last 6 Months Sales =
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        CalendarTable[Date],
        LASTDATE ( CalendarTable[Date] ),
        -6,
        MONTH
    )
)

[Jerryvb]

Thank you that is helpfull. But I need to be able to do this without changing the source files and really need it per month.

[v-caliao-msft]

@Jerryvb, You can create a return the months different between current day and sales date.
Months = IF(YEAR(TODAY())=YEAR('Date'[CalendarDate]),MONTH(TODAY())-MONTH('Date'[CalendarDate])+1,(YEAR(TODAY())-YEAR('Date'[CalendarDate]))*12+MONTH(TODAY())-MONTH('Date'[CalendarDate])+1)

and then add this calculated column to filter

Regards,

Charlie Liao