Forum Discussion

ClemFandango's avatar
ClemFandango
Icon for Advocate II rankAdvocate II
2 years ago
Solved

Populate 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...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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")
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

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")
ClemFandango's avatar
ClemFandango
Icon for Advocate II rankAdvocate II
2 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    You are welcome.