cancel
Showing results for
Search instead 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
if(Equitys[TradeDate] >=_min && Equitys[TradeDate] <= _date, true(), false())

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

date as dateadd : https://www.youtube.com/watch?v=9qiRivlBv8w

Default date :https://www.youtube.com/watch?v=hfn05preQYA

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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
if(Equitys[TradeDate] >=_min && Equitys[TradeDate] <= _date, true(), false())

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

date as dateadd : https://www.youtube.com/watch?v=9qiRivlBv8w

Default date :https://www.youtube.com/watch?v=hfn05preQYA

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I

thank you for your reply.
I do not need the Total sales.
I use the calculated column as a filter/slicer.

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors