Forum Discussion

souvik900766's avatar
souvik900766
Icon for Helper IV rankHelper IV
5 years ago

Return data from specified time interval in all rows in a calculated column

 
 

Hi !

My Data:-

YearMonth CY2018
2019     Jan,19        784003.6     
2020Jan,20796923 
2021Jan,21912686.3 
2018Jan,18754578.3 

I want to return year 2018 CY Value i.e. 754578 in 2018 Column in all rows.
The calculation should be based on latest month for e.g.
MAX(Month),dateadd(month,-3,year)
So, here I want to find from Jan, 2021, to 3 years prior data.
I tried with this three but not working..

Thanks in advance !

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    souvik900766 , Try like given examples

     

    Example

    sumx(filter(Table, Table[year] = 2018 ), [CY])

     

    sumx(filter(Table, Table[year] = 2018 && [Month] = "Jan, 18" ), [CY])

     

    if you can only month name like Jan, feb or 01, 02 etc

     

    sumx(filter(Table, Table[year] = 2018 && [Month] = earlier([Month] )  ), [CY])