Forum Discussion

kevinedora's avatar
kevinedora
Icon for Helper I rankHelper I
4 years ago
Solved

Return value from 3 months ago

Hi,

 

I'm trying to add another column to "Table 1" basically saying what's the latest movement from 3 months ago per account(it should be "installs" only). Table 2 should be the lookup table. Criteria should be the same account no. and item.

 

I wanted this to be in a matrix, so i'm not sure if calc column should be the correct response or measure maybe? Please advise. Thank you!

 

Table 1

Period (YYYY-MM-DD)Account no.Item
202203016173262992SFEM
202203016173262991SFEM

 

Table 2

Period (YYYY-MM-DD)Account no.MovementItem
20220102617326299

Installs

2SFEM
20220103617326299Recon2SFEM
20220104617326299Discon1SFEM

 

Result

 

Period (YYYY-MM-DD)Account no.ItemInstalls 3Months
202203016173262992SFEMYes
202203016173262991SFEMNo
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    kevinedora 

    is this what you want

    Column =
    VAR _install=maxx(FILTER('Table 2','Table 1'[Account no.]='Table 2'[Account no.] &&'Table 1'[Item]='Table 2'[Item] && 'Table 2'[Movement]="installs" && year('Table 1'[Period (YYYY-MM-DD)])=year('Table 2'[Period (YYYY-MM-DD)])&&month('Table 1'[Period (YYYY-MM-DD)])=month('Table 2'[Period (YYYY-MM-DD)])+2),'Table 2'[Movement])
    return if(_install="","N","Y")
     
    you can change +2 to +1 or remove it to get 2 month ago or within the month

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    kevinedora 

    not clear about the 3 month ago. it looks like the date of install is only two month ago. could you pls clarify more about this?

     

    maybe you can try below DAX to create a column

    Column = 
VAR _install=maxx(FILTER('Table 2','Table 1'[Account no.]='Table 2'[Account no.] &&'Table 1'[Item]='Table 2'[Item] && 'Table 2'[Movement]="installs"),'Table 2'[Movement])
return if(_install="","N","Y")

    • kevinedora's avatar
      kevinedora
      Icon for Helper I rankHelper I
      4 years ago

      Hello ryan_mayu 

       

      Thanks for the response. Apologies for the confusion, the example above should be correct since I also count march from the "3 months" ago criteria.

      Also, from your calc column, what if the account had been installed in December 2021? the formula would return "Yes". I just need specifically from 3 months ago (January).

      In the future, I might use the formula also for the "2 months ago" criteria or "within the month". I hope this clarifies my concern.

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        4 years ago

        kevinedora 

        is this what you want

        Column =
        VAR _install=maxx(FILTER('Table 2','Table 1'[Account no.]='Table 2'[Account no.] &&'Table 1'[Item]='Table 2'[Item] && 'Table 2'[Movement]="installs" && year('Table 1'[Period (YYYY-MM-DD)])=year('Table 2'[Period (YYYY-MM-DD)])&&month('Table 1'[Period (YYYY-MM-DD)])=month('Table 2'[Period (YYYY-MM-DD)])+2),'Table 2'[Movement])
        return if(_install="","N","Y")
         
        you can change +2 to +1 or remove it to get 2 month ago or within the month