Forum Discussion
Helper IHow to use a selected date value to filter a data table?
Hello,
My challenge is: My customer have a Year-Month filter which allows him to select only one period that he wants to see the data. All the visuals should show data only from this selected period, except by one visual, which should show data from the selected Year-month and also from the last 5 Months (i.e. data from the last 6 months based on the month selected). If you have any idea how to make this work, i don't actually need the solution for the paragraphs below. 😃
The way i thought to solve this was creating an alternative date table using the functions FILTER and SELECTEDVALUE (yet no idea how to make it filter the last 6 months, only the selected month) but its not working.
Here is the code:
6 Months Calendar = FILTER(Calendar, Calendar[Year-Month] = SELECTEDVALUE(Calendar[Year-Month]))
If i manually input the same Year-Month value, lets say 202008 (August 2020) it works, but when i use the SELECTEDVALUE function it just didn't work. The Year-Month column is a number, which i used the following code to make:
Year-Month = VALUE(Calendar[Year] & if(Calendar[Month]<10,0&Calendar[Month],Calendar[Month]))
(Need to make this to explicit makes e.g. August 2020 bigger than November 2019 (because originaly 20208 < 201911)
I created a measure only with that SELECTEDVALUE formula piece and it's getting the value, which makes me more confuse. Also again, not even yet figurated out an way too make this filter ont only the selected value but the last 6 periods.
Any help is welcome, thanks.
My question was more about the axis and it is what I feared that you have a date component as the chart axis. The only way I know of to get that to work is to combine a measure like I wrote with a secondary date table. In this example I have created a copy of the date table with this code.
Dates2 = 'Date'The slicer is from the primary date table. The Month column in the chart and grid are from the second date table and you can see that the [Last 6 months] measure returns correctly. Having the secondary date table allows the [month] column in the visual to not be filtered buy the section made on the main date table. Then the measure handles only returning data for those 6 months and it aligns correctly.
You need to join the secondary date table into your model the same way the primary date table is joined in.
10 Replies
- jdbuchanan71
Super User
Can you tell us what data is displayed in the visual? If it is, for example, sales amount by brand you can write a measure that shows sales for the last 6 months based on the month year selected like so.
Last 6 months = VAR _LastDate = LASTDATE('Date'[Date]) RETURN CALCULATE([Sales Amount],DATESINPERIOD('Date'[Date],_LastDate,-6,MONTH))
tfralettiHi jdbuchanan71 ,
The value itself is not important, consider it as a metric that i already have calculated. What i need is: based on a selected date value, actually not a date itself, but a representation of a period (like August 2020) on a filter, use this selected value and its previous five distinct values (Jule 2020, June 2020, ... ) in a bar chart, which each bar is a period.
Based on your explanation and print, it should work, but unfortunatelly, when i filter, it still only shows the data from the selected period. Maybe if we change a little bit your suggested solution from LASTDATE to SELECTEDVALUE it may work? I tried but probably made some mistake, because it didn't work.
- jdbuchanan71
My question was more about the axis and it is what I feared that you have a date component as the chart axis. The only way I know of to get that to work is to combine a measure like I wrote with a secondary date table. In this example I have created a copy of the date table with this code.
Dates2 = 'Date'The slicer is from the primary date table. The Month column in the chart and grid are from the second date table and you can see that the [Last 6 months] measure returns correctly. Having the secondary date table allows the [month] column in the visual to not be filtered buy the section made on the main date table. Then the measure handles only returning data for those 6 months and it aligns correctly.
You need to join the secondary date table into your model the same way the primary date table is joined in.
- Ashish_Mathur
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
