Marking date if it falls in the last 6 months - Only have partial answer

[NOVICE02]

Hi there,

I have a simple problem and I can't seem to figure it out. I have Year to date information that i am displaying but for a few visuals I only want to show the data if it falls in the 6 months prior to the previous month.

i.e. In september, i only want to show data between February and July. And so on... 

I understand that I will need to create a measure to mark all the dates that fall in this timeline and this is what I am did

Report_Month = IF(and(month(Date_Calendar[Date]) <= MONTH(today())-2,Month(Date_Calendar[Date]) >= MONTH(today())-7), 1,0)

But , when I check the data, it will fail once dates start overlapping years... Is there a way to modify this ? or is a completely different measure. Appreciate any help

[amitchandak]

@NOVICE02 , Try a measure like with help from date table

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ], eomonth(MAX('Date'[Date ]), -1) ,-6,MONTH))

[NOVICE02 · Accepted solution]

Thanks Amit, I actually found a solution:

Report_Month =

var monthDiff = (YEAR(TODAY()) - YEAR(Date_Calendar[Date]))*12 + MONTH(TODAY()) - MONTH(Date_Calendar[Date])

return IF(monthDiff <= 1,"Last 2 Month",

IF(monthDiff < 8, "Last 6 month",

IF(monthDiff <10,"Last 9 month",

"more than a year")))

But I will use yours as well for reference. Appreciate it