cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
achude
Helper I
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

@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!

View solution in original post

CNENFRNL
Super User III
Super User III

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

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

View solution in original post

7 REPLIES 7
CNENFRNL
Super User III
Super User III

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

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

View solution in original post

this works perfect thank you

sanalytics
Responsive Resident
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

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.

amitchandak
Super User IV
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!

@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!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors