Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

summarized table with a dynamic filter Is it possible?

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)
 
)
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

5 Replies

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

    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

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

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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...
     
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    ah... 

     

    So in a measure how will  this look?