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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.