Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Hi Alex, thanks for your help. Finally, I could generate a new table and calculate some measures from there.
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
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
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.