Get Last 6 months And Next 6 months from current month

[Anonymous]

I am trying to show the last 6 months and next 6 months data in charts.

In my table i have the dates as below.

Month Year Table

Now in my chart i would like to show data of current month to last 6 months and next 6 months.

I have written below calculated column and it is calculating the last 6 months and next 6 months regardless of the year.

Last&next6months = IF(AND(MONTH('Month Year'[Date])>=MONTH(TODAY())-6,MONTH('Month Year'[Date])<=MONTH(TODAY())),"Last 6 months",
	IF(AND(MONTH('Month Year'[Date])>=MONTH(TODAY()),MONTH('Month Year'[Date])<=MONTH(TODAY())-12),"","Next 6 months"))

Output:-

Any better solution or any suggestions for this.

Thanks,

Mohan V

[Sidra-Muajhid]

hi 

I used same DAX for next six months display. it didnt work. 

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may add a calculated column as shown below.

Column =
VAR MonthDiff =
    IF (
        'Month Year'[Date] <= TODAY (),
        - DATEDIFF ( 'Month Year'[Date], TODAY (), MONTH ),
        DATEDIFF ( TODAY (), 'Month Year'[Date], MONTH )
    )
RETURN
    SWITCH (
        TRUE (),
        MonthDiff >= -5
            && MonthDiff <= 0, "Last 6 months",
        MonthDiff >= 1
            && MonthDiff <= 6, "Next 6 months",
        "other"
    )