Forum Discussion
ClemFandango
Advocate II
Advocate IIPopulate column based on month and value in another column using DAX
Hi all, I am trying to create a calculated column that returns “yes” for the whole month (of the date specified in date column below) where any ‘Ref’ has a value of 1 in ‘New’. The data table loo...
Hi,
Write this calculated column formula
Year = year(Data[Date])
Edit this formula
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Month number]=EARLIER(Data[Month number])&&[Year]=EARLIER(Data[Year])&&Data[Ref]=EARLIER(Data[Ref])&&Data[New]=1)),"Yes","No")
ClemFandangoAdvocate II
Advocate IIHi Ashish_Mathur & Anonymous
Huge thanks for both of your responses.
Apologies Ashish_Mathur , i missed something out, is there anyway of amending the above so it returns "Yes" based on both the year & month? i.e not just March, but March 2020?
All help is greatly appreciated
Thanks again
Ashish_Mathur
Super User
Super UserHi,
Write this calculated column formula
Year = year(Data[Date])
Edit this formula
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Month number]=EARLIER(Data[Month number])&&[Year]=EARLIER(Data[Year])&&Data[Ref]=EARLIER(Data[Ref])&&Data[New]=1)),"Yes","No")- ClemFandango
Amazing Ashish_Mathur !
I also found that I could do this by creating column YearMonth instead of Year
YearMonth = YEAR(Data[Date])*100+Month(Data[Date])This provides a numeric version of Year & Month combined.Again thanks to Ashish_Mathur & Anonymous for all your help- Ashish_Mathur
You are welcome.