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 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!

 

DateRef     New     RefNewInMonth?
01/01/2020    1000     1Yes
02/01/20201000 Yes
01/01/20202000  
01/01/20233234 Yes
02/01/202332341Yes
03/01/20233234 Yes
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")

7 Replies

  • 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

  • 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.

     

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

    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'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