How to use a selected date value to filter a data table?

[tfraletti]

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.

[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.

[jdbuchanan71]

@tfraletti 

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))

[tfraletti]

Hi @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 · Accepted solution]

@tfraletti 

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.

[tfraletti]

@jdbuchanan71 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.

[tfraletti]

@jdbuchanan71 Just created a test dataset just to test your solution and it works perfectly. Probably there is something on else on that project dataset that is making it impossible. Thank you very very much!