Forum Discussion
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 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.
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
Hi Alex, thanks for your help. Finally, I could generate a new table and calculate some measures from there.
6 Replies
Sounds like you need something along the lines of ALLEXCEPT where get rid of your "date" context filter but keep everything else.
I tried, but not works. I dont know how to use ALLEXCEPT function correctly. Can you help me?
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
AlexThanks 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
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
