Hi All, I am trying to get next year value (dynamically through Year Slicer), but not able to do that. I am using below DAX to calculate next year value. It is returning blank. Measure = CALCULATE(SUM(Sheet5[Value]),FILTER(Sheet5,Sheet5[Year] = ALLSELECTED(Sheet5[Year])+1)) I also have slicer on Year. I also tried to use other functions like NEXTYEAR, but not able to do it. I think FILTER() is not accepting any measure or calculation (like +1). Could someone help on this one? Thank you in advance! Regards,Niket Talati

hi Anonymous This should works Medida-SUMofValues-NextYear =VAR Next_Year = IFERROR ( VALUES ( 'Tabla Años'[Year] ) + 1; BLANK () )RETURN CALCULATE ( SUM ( 'Tabla Años'[Value] ); 'Tabla Años'[Year] = Next_Year Let me know.

Next Year calculation (dynamically) through FILTER() and Slicer

16 Replies

ankitpatira
Community Champion
10 years ago
Anonymous so for example, are you trying to get value 2016 when you select 2015 in Slicer ?
- Anonymous
  Not applicable
  10 years ago
  Hi Ankit,
  
  Thanks for your quick response. Yes, when I select 2015 in slicer value, then 2016 should be passed as filter in the table do the calculation. I also have one date master table and I tried all the options, but doesn't work.
  
  Regards,
  Niket Talati
sdjensen
Solution Sage
10 years ago
Hi,

Try this formula
CALCULATE ( SUM( 'Sheet5'[Value] ); PARALLELPERIOD( Periode[Date]; 12; MONTH ) )

I am sure you will need a seperate date table to have the time intelligence function working properly. If you dont already have a date table you could try to read here... https://www.powerquery.training/portfolio/dynamic-calendar-table/
- Anonymous
  Not applicable
  10 years ago
  Hi Sdjensen,
  
  Thanks for your response.
  
  I have a date master table and I tried your formula, but doesn't work for me. I have Year slicer and according to the selected year, next year (suppose I select 2015, then 2016 should be passed in that DAX) should be passed in DAX and do the calculation.
  
  Regards,
  Niket Talati
Baskar
Resident Rockstar
10 years ago
Hi Talat,

Please check with the DAX Query

Next Year Value = CALCULATE(SUM('Date Master'[DayOfMonth]),PARALLELPERIOD('Date Master'[Date].[Date],-1,YEAR))

Let me know it working fine...

Note :
Prob with your query is once the context filter apply then next year value we can get it. Tha is the issue.
- Anonymous
  Not applicable
  10 years ago
  Hi Baskar,
  
  Thanks for your response.
  
  I am not able to get what you have written. I have date master table but the thing is next year should be calculate based on the current selected year from Slicer. Could you explain your formula more in detail (in perspective to Year Slicer selection)?
  
  Regards,
  Niket Talati
  - Baskar
    Resident Rockstar
    10 years ago
    Next Year Value =
    CALCULATE( SUM(Value) ,PARALLELPERIOD("Your Date Field",-1,YEAR))

Forum Discussion

Next Year calculation (dynamically) through FILTER() and Slicer