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 these calculated column formulas

Month number = MONTH(Data[Date])
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Month number]=EARLIER(Data[Month number])&&Data[Ref]=EARLIER(Data[Ref])&&Data[New]=1)),"Yes","No")

Hope this helps.