Retrieve month only from date slicer for trend visual

[Anonymous]

I would appreciate your help on below feauture I would like to use. I believe we need something to enable selective relationship filtering between filters and visuals on a page. Now it is only possible to include all dimensions captured through a filter selection or none.

Nevertheless, there should be something possible to achieve the following:

I have a daily sales report for which the user is able to select their day date (to look at daily sales of prior days). On the same page I have a trend visual which should show the trend line of the particular month. That means if a date somewhere in February 2019 is selected, the trend visual should show the entire month of February 2019 on the axis. 

If the user selects a random day in December 2018, the trend axis should change to full December 2018.

I would like to retrieve the month and year only from the date (day) slicer, but at the moment it only allows to filter on the full date or none (showing all months available on the axis). 

I have tried things as SELECTEDVALUE() to retrieve the selected month but I find no way to make this interact with the date axis in my trend visual. 

I believe I am not the only one struggling with the inability to further choose what dimensions should be picked up in a filter. I would be very happy to upvote any other wish dealing with this to gain attention. Power BI is an awesome tool, but this essential feature is clearly something for which Tableau still stands out.

Thanks in advance!

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below steps:

Sample data(Date from 2018.1.1 to 2018.2.28):

Create a new calculated table:

Calculated Table = VALUES(Table1[Date])

Create measure:

Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1',MONTH('Table1'[Date])=MONTH(SELECTEDVALUE('Calculated Table'[Date]))))

Use the [Date] column which is in the calculated table as slicer and you could see the result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous · Accepted solution]

I found the most easiest solution to be this. Found it myself:

Create this measure:

Flag measure = year(max('Date'[Date])) &"_"& month(max('date'[date]))

This gives the maximum year with month combined in a text value, for example "2019_01".

Then in your visual, order date by Top 1N by this flag measure. 

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below steps:

Sample data(Date from 2018.1.1 to 2018.2.28):

Create a new calculated table:

Calculated Table = VALUES(Table1[Date])

Create measure:

Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1',MONTH('Table1'[Date])=MONTH(SELECTEDVALUE('Calculated Table'[Date]))))

Use the [Date] column which is in the calculated table as slicer and you could see the result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[andres777]

this is nice, thanks!

But if you have 3-4 year's worth of data, would it return SUM values for each month for all years ?

[andres777]

would this work if you have 3-4 years worth of data ? 

It may sum values for the same month for every year, right ?

[Anonymous]

Thanks for your answer. I was actually hoping on beforehand there would a way to do the same without creating calculated tables. But it works!

[Anonymous]

Please feel free to suggest any other path to achieve this but I have tried the following with little succes yet:

Monthselection = month(selectedvalue('datetable'[date]))

Yearselection = year(selectedvalue('datetable'[date]))

Sales trend measure =
VAR monthselector = [Monthselection]

VAR yearselector = [Yearselection]

Return

           Calculate([SALES];
           'Date'[Monthnumber] = monthselector;

           'Date'[Year] = yearselector

Then when I create a table with the [Sales trend measure] and a date slicer (for which I set the relationship to the table to non), I still see the all present month for the [Sales trend measure]. In my eyes it seems like [Sales trend measure] is calculated before the date filter is taken into consideration. Therefore I still see Sales for more months than what the month of the date I have selected in the date slicer. 
If I have hardcode a value 1 (indicating January) or 2 (February) instead of the VAR monthselector (in the Sales trend measure above), it works...

If I create a new measure which shows only the just created VAR monthselector, it works in accordance to the date selection in the filter. This looks like this:

Variable monthselector =

VAR monthselector = [Month selection]

Return

monthselector

This returns a 1 when I select a random date in January and a 2 in February (I think you get the point).

Why is it then not working combined in above measure?

Please help me complete this! Thank you so much in advance.