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")
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,ClemFandango 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

RefNewInMonth = 
VAR currentdate =MAX('ref'[Date])
VAR currentmonth=MONTH(currentdate)
VAR currentyear=YEAR(currentdate)
VAR mewref=CALCULATE(COUNTROWS('ref'),FILTER(ALLSELECTED('ref'),YEAR('ref'[Date])=currentyear&&MONTH('ref'[Date])=currentmonth&&'ref'[Ref]=MAX('ref'[Ref])&&'ref'[New]=1))>0
RETURN IF(mewref,"yes",BLANK())

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi Anonymous 

Thanks for this. Is there any way of getting the same results from 'RefNewInMonth' in a calculated column instead of a measure? This is due the column being required for future calcs.

 

All help hugely appreciated, thanks again