Forum Discussion
Date filter selection issue - graphs with different need
Hi,
I'm struggling to use a date slicer (Month and Year) to work properly with my different graphs.
1. I have a Date table, see below:
2. I have a Fact table with all transactions, linked to the Date table
3. I use a Month Year Date slicer for the user to choose the month in review
4. For one table, I want to show Jan-Dec for the specific year that the user choose in the drill-down. In this case Jan-Dec for 2019 should be visible. Here is where I get the first problem. I use "MonthYear" in the slicer and also the table below. If I make a connection between the slicer and table, it will only show nov 2019. Therefore, I would like to add another filter for the table - to use the SELECTEDVALUE for year and use that as a visual filter in the table (still using MonthYear to get the months displayed). Can I use some sort of measure to capture the SELECTEDVALUE and then use the column "Year" from the Date-table to only show MonthYear for Year = 2019 (in this case)?
5. My other issue is the KPI im using, where I would like to use all the months up to the selected month, i.e. in this case I would like to use Jan-Nov 2019
Many thanks for any input
Thanks PaulDBrown . I tried a few approaches and managed to solve the issue with one table using disconnected Date-table and a measure to filter the original date table using selected value (A, below). However, I have several tables and graphs with different values (MTD, YTD, %-differences etc). If I would use the same approach for all tables I would need to re-create 50+ measures.
I hoped that I could use a measure to filter a whole table (with several values). However, I didnt work as planned (B, below).
A - My approach below (solution for one table with one value):
-----------------------------------------------------------------
1. Measure (all months in selected Month-Year)
2. Result (selecting Aug 2019 and I get all months during 2019)
B - My failed approach to use a measure filter to filter a whole table/graph with several values:
---------------------------------------------------------------------
1. Using measure in the visual filter (returing 1 if table should show the corresponding MonthYears)
I thought I could create a visual filter using a measure, where the measure return "1" if the MonthsYears are included in the selected set (same logic as before, selecting Aug2019 should return Jan-Dec during 2019 of MonthYear). However, I dont receive any values.
Please let me know if you see any errors or have a smarter approach. Would too much work to create 50+ measures to filter some tables with 10+ values.
Many thanks
10 Replies
Have you tried edit interaction to solve this problem ?
Yes, but the problem is if I use the interaction between the slicer (MonthYear) and the table, the table only show "Nov 2019" in this case. If I dont use the interaction, the table shows all the Months and Years available. I just want the table to show Jan-Dec 2019 - if the user select "Nov 2019" in the slicer and Jan-Dec 2020 - if the user select e.g. "June 2020" in the slicer. Somehow I need to pick up the year choosen by the user (in this case 2019) and then visually filter the table to show all the months in 2019.
For the KPI issue, I need to have Jan-"choosen month", in this case Jan-Nov 2019 (filtered in the KPI).
Anonymous
A common technique is to use a disconnected period table. Check out this thread in which the OP was after something very similar:
Thanks PaulDBrown . I tried a few approaches and managed to solve the issue with one table using disconnected Date-table and a measure to filter the original date table using selected value (A, below). However, I have several tables and graphs with different values (MTD, YTD, %-differences etc). If I would use the same approach for all tables I would need to re-create 50+ measures.
I hoped that I could use a measure to filter a whole table (with several values). However, I didnt work as planned (B, below).
A - My approach below (solution for one table with one value):
-----------------------------------------------------------------
1. Measure (all months in selected Month-Year)
2. Result (selecting Aug 2019 and I get all months during 2019)
B - My failed approach to use a measure filter to filter a whole table/graph with several values:
---------------------------------------------------------------------
1. Using measure in the visual filter (returing 1 if table should show the corresponding MonthYears)
I thought I could create a visual filter using a measure, where the measure return "1" if the MonthsYears are included in the selected set (same logic as before, selecting Aug2019 should return Jan-Dec during 2019 of MonthYear). However, I dont receive any values.
Please let me know if you see any errors or have a smarter approach. Would too much work to create 50+ measures to filter some tables with 10+ values.
Many thanks
