cancel
Showing results for
Did you mean:
Helper I

## calculated column - last six months

i created a calculated colum, to indicate if a sale was made in the last six months. i used this query.

``Last6months = IF(MONTH(Equitys[TradeDate]) >=MONTH(TODAY()) -6 && YEAR(TODAY()) = YEAR(Equitys[TradeDate]),TRUE(),FALSE())``

it was working fine before. but with the new year, its broken. as all rows are now false. how do i fix this?

the calulated column is being used as a filter/slicer

2 ACCEPTED SOLUTIONS
Super User IV

@achude , Try a new column

Is Last 6 Month =
var _date = today()
var _min = date(year(_date), month(_date)-6,day(_date))
return

you case use in place today
this month end : var _date = eomonth(today() ,0)
last month end : var _date = eomonth(today() ,-1)

refer my video

Proud to be a Super User!

Super User III

@achude , you might want to this formula in a calculated column

``Last6months = Equitys[TradeDate]>=EDATE(TODAY(),-6)``
7 REPLIES 7
Super User III

@achude , you might want to this formula in a calculated column

``Last6months = Equitys[TradeDate]>=EDATE(TODAY(),-6)``
Helper I

this works perfect thank you

Responsive Resident

Hello @achude

Your formula should return the correct result..Dont understand the your exact requirement.Can you please send us the pbix file and explain clearly your requirement so the we can work..

regards

sanalytics

Helper I

i might not be able to provide the Pbix file. i can try to explain what i am talking about.

i need a calculated colum that has "true" or 'false' of the date coulmn is within the last 6 months.

Super User IV

@achude . with help from a date table

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

or

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

or

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Proud to be a Super User!

Super User IV

@achude , Try a new column

Is Last 6 Month =
var _date = today()
var _min = date(year(_date), month(_date)-6,day(_date))
return

you case use in place today
this month end : var _date = eomonth(today() ,0)
last month end : var _date = eomonth(today() ,-1)

refer my video

Proud to be a Super User!

Helper I

I do not need the Total sales.
I use the calculated column as a filter/slicer.

Announcements