Forum Discussion

pfprado's avatar
pfprado
Frequent Visitor
10 years ago
Solved

Get values outside the range selected

I need calculate a measure by month from a fact table, but outside the filter range, ie, I can filter the fact table and display the results in some graphics but I need a chart with the sum of data v...
Need Help
  • AlexChen's avatar
    AlexChen
    10 years ago

    Hi,

     

    This is not issue of  "out of range" table. I meaned that in the formula

     

    Table = SUMMARIZE(Filter(Test, Test[date] > Date(2016, 7, 31)), Test[Year], Test[Month Name], "monthAmount", sum(Test[amount]))

     

    the value "Date(2016, 7, 31)"  has to be a fixed value instead of a dynamic value here. You have to calculate it manually and put it here.

    Your plan to make a slicer to filter records in a table dynamically is not supported currently.

     

    Best Regards

    Alex

  • pfprado's avatar
    pfprado
    10 years ago

    Hi Alex, thanks for your help. Finally, I could generate a new table and calculate some measures from there.

AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

Hi,

 

We are not able to create a summarized table based on what you selected in a slicer currently.

 

I assume you have a table like below. 

 

 

You need to create a year column and a month name column from “date” column in query editor.

 

 

Now you can create a summarized table with formula below if your max selected date is 20160731.

 

Table = SUMMARIZE(Filter(Test, Test[date] > Date(2016, 7, 31)), Test[Year], Test[Month Name], "monthAmount", sum(Test[amount]))

 


The “Date(2016, 7, 31)” in the formula above can’t not be replaced with a dynamic formula  to correspond with what your selected in a slicer. So in this scenario, I suggest you generate that “out of range” table separately, or create another slicer to filter the “out of range” data.

 

Best Regards
Alex

pfprado's avatar
pfprado
Frequent Visitor
10 years ago

Thanks Alex for your help. In this case I need generate a new out of range table, can you help me please?, i am new in powerbi desktop

  • AlexChen's avatar
    AlexChen
    Microsoft Employee
    10 years ago

    Hi,

     

    This is not issue of  "out of range" table. I meaned that in the formula

     

    Table = SUMMARIZE(Filter(Test, Test[date] > Date(2016, 7, 31)), Test[Year], Test[Month Name], "monthAmount", sum(Test[amount]))

     

    the value "Date(2016, 7, 31)"  has to be a fixed value instead of a dynamic value here. You have to calculate it manually and put it here.

    Your plan to make a slicer to filter records in a table dynamically is not supported currently.

     

    Best Regards

    Alex

    • pfprado's avatar
      pfprado
      Frequent Visitor
      10 years ago

      Hi Alex, thanks for your help. Finally, I could generate a new table and calculate some measures from there.