Get values outside the range selected

[pfprado]

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 values of the table that are greater than the end date of the selected range, like a projection:

Initial date: 20160101
Final date: 20160731

I need the sum of data values of the table by month that are greater than 20160731:

Value       Year       Month
10000     2016      August
15000     2016      September
8000       2016      October

Thanks for your help.

[AlexChen · Accepted solution]

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

[AlexChen]

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]

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 · Accepted solution]

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 · Accepted solution]

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

[Greg_Deckler]

Sounds like you need something along the lines of ALLEXCEPT where get rid of your "date" context filter but keep everything else.

[pfprado]

I tried, but not works. I dont know how to use ALLEXCEPT function correctly. Can you help me?