Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

@Anonymous , 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

View solution in original post

CNENFRNL
Community Champion
Community Champion

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

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

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

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

this works perfect thank you

sanalytics
Solution Supplier
Solution Supplier

Hello @Anonymous 

 

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

Anonymous
Not applicable

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
Super User

@Anonymous . 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.

@Anonymous , 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

Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.