topic Re: summarized table with a dynamic filter Is it possible? in DAX Commands and Tips

summarized table with a dynamic filter Is it possible?

Anonymous — Mon, 07 Dec 2020 03:32:33 GMT

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)

)

Re: summarized table with a dynamic filter Is it possible?

Fowmy — Mon, 07 Dec 2020 05:42:28 GMT

Anonymous

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

Re: summarized table with a dynamic filter Is it possible?

Anonymous — Mon, 07 Dec 2020 07:34:23 GMT

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...

)

Re: summarized table with a dynamic filter Is it possible?

Fowmy — Mon, 07 Dec 2020 07:55:27 GMT

Anonymous

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

Re: summarized table with a dynamic filter Is it possible?

Anonymous — Mon, 07 Dec 2020 07:59:04 GMT

ah...

So in a measure how will this look?

Re: summarized table with a dynamic filter Is it possible?

angelarojasmora — Wed, 25 May 2022 16:49:56 GMT

Thanks! This was really useful