summarized table with a dynamic filter Is it possible?

[mgtaylor3]

Hi , I need some help to create the following summarized table.

I have a revenue_data table i need to summarize .  At the moment I have to manually enter the 2019  to get this table to run.

 I'd like it to run when i choose a Year from a slicer if possible?  to be even better, to choose the previous year of the chosen year.

 so 2020   I'd like 2019  from the code below can it be done?

March_Table =

CALCULATETABLE(

SUMMARIZE(

Revenue_Data,

[Country],[TERRITORY_DETAILED_DESCRIPTION],[BASE_AMOUNT],[DATE])

,FILTER('Revenue_Data',

year(Revenue_Data[DATE])= 2019)

)

[mgtaylor3]

ah... 

So in a measure how will  this look?

[Fowmy]

@mgtaylor3 

if you are creating a calculated table, you cannot include the slicer values coming from your report. If you are going to use this code inside a measure, it will work.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mgtaylor3]

Ok  Unfortunately not....yet... let me change it a wee bit.

March_Table =

CALCULATETABLE(

SUMMARIZE(

Revenue_Data,

[Country],[TERRITORY_DETAILED_DESCRIPTION],[BASE_AMOUNT],[DATE],Revenue_Data[FIN_YEAR])

,FILTER('Revenue_Data',

Revenue_Data[FIN_YEAR]= SELECTEDVALUE('Calendar'[FIN_YEAR])-1

)

So I'm trying this.... 

I have on a page a slicer for financial year.... I shoose 2020  and i need that to be 2019 in the filter.. so the assumption is the selected value does this?

 there is a link the the calendar table  from the original revenue_data table.

I just can't get this to work...

)

[Fowmy · Accepted solution]

@mgtaylor3 

Try this code please:

March_Table =
CALCULATETABLE(
SUMMARIZE(
Revenue_Data,
[Country],[TERRITORY_DETAILED_DESCRIPTION],[BASE_AMOUNT],[DATE])
,FILTER('Revenue_Data',
year(Revenue_Data[DATE])= YEAR(SELECTEDVALUE(Revenue_Data[DATE]))-1)
 
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[angelarojasmora]

Thanks! This was really useful