Populate column based on month and value in another column using DAX

[ClemFandango]

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 looks like below and the ‘Ref’ is a text column and 'New' is Number. I am trying to recreate ‘RefNewInMonth?’ as a new column in DAX that returns either “Yes” or blank based on conditions in both 'Ref', 'New' & 'Date'. I have 14 million rows+ so hoping for something that is as efficient as possible.

Any ideas greatly appreciated!

Date	Ref	New	RefNewInMonth?
01/01/2020	1000	1	Yes
02/01/2020	1000		Yes
01/01/2020	2000
01/01/2023	3234		Yes
02/01/2023	3234	1	Yes
03/01/2023	3234		Yes

[ClemFandango]

Hi @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 · Accepted solution]

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]

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.

[Ashish_Mathur]

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.

[Anonymous]

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]

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