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.