Forum Discussion
Helper IHow to use a selected date value to filter a data table?
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.
Super UserMy 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.
Helper Ijdbuchanan71 Yes, this is exactly what i need! But i am not sure why its not working in here. Here is what i did:
1. Created the measure you suggested and change the relevant fieds on it (date field);
2. Created a copy of the original date table;
3. Created the relevant relationships on it to make the graphic render;
4. Used the Year-Month column of the original date table as the filter;
5. Used the Year-Month column of the copy date table as the bar axis;
6. Changed the measure on the bar graphic to use the created measure.
When i made a selection in the filter the bars that don't match with the selected value still desapears. I am missing something?
- jdbuchanan71
In the measure, the references to the date table should all be to the primary date table. Is that the case in your updated measure?
Last 6 months = VAR _LastDate = LASTDATE('Date'[Date]) RETURN CALCULATE([Sales Amount],DATESINPERIOD('Date'[Date],_LastDate,-6,MONTH))- tfraletti
jdbuchanan71 Yes it is. I am guessing that the problem may be in the relationships. All the relationships have both direction effect. I am checking if i can change this (its a project that i just got involved, didn't know the details).
EDIT: Changed it but yet not working. Trying to figure out any other reasons.
- jdbuchanan71
Not sure it will help but here is the layout of my sample with the extra date table highlighted.