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

[Anonymous]

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

[Baskar]

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]

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]

Next Year Value =

CALCULATE( SUM(Value)  ,PARALLELPERIOD("Your Date Field",-1,YEAR))

[Anonymous]

Hi Baskar,

Thanks for the prompt response again.

But it doesn't work either since I do not have continuos dates in the dataset. Here I have attached the screenshot how my data looks. I do not have data for all the dates. 

How I want - When I select 2016 in Slicer then, it should give me 198840229.71. I hope now I explained my issue propoerly.

Regards,

Niket Talati

[Baskar]

From that date you could select as year,

then apply the cacl measure

Measure = CALCULATE(SUM("STD PACK"),PARALLELPERIOD("Date Filed",1,YEAR))

Surely its should work , bec its working for me perfectly....

[Anonymous]

Hi Baskar,

I followed the same steps, but still it is returning blank. I have attached below screenshot.

DataReport

Please let me know where I am wrong?

Thank you,

Niket Talati

[Baskar]

Cool Niket, 

 I come with solution for you.

File --> Oprions and Settings --> Options 

Choose Preview Features

And here u need to enable the Inline Hirerachy Labels 

now check the result. again if not try to create new report in new Page.....

[Anonymous]

Hi Baskar,

This also doesn't work. I tried to create a new report but same issue, returning blank.

Can I send you the pbix file? Can you share your email id?

Thanks again for helping.

Regards,

Niket Talati

[Vvelarde · Accepted solution]

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.

[Anonymous]

Thank you bro! It works!!

[Baskar]

vbaskar3@gmail.com

Can u please share how to u solved the issue it will help others.

if okay then close the ticket

[Anonymous]

Hi Baskar,

One of the provided solution worked. I have selected the correct answer. 

Regards,

Niket Talati

[sdjensen]

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]

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

[ankitpatira]

@Anonymous so for example, are you trying to get value 2016 when you select 2015 in Slicer ?

[Anonymous]

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