Date filter selection issue - graphs with different need

[Anonymous]

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

[PaulDBrown]

@Anonymous 

A common technique is to use a disconnected period table. Check out this thread in which the OP was after something very similar:

Showing las 12 months based on Slicer selection 

[Anonymous · Accepted solution]

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

[Anonymous]

Anyone that could help me on this? Still stuck on finding an efficient solution to all my graphs? 

Thanks

[PaulDBrown]

@Anonymous 

Apologies since on re-reading your post on using the filter pane, I did not address your issue. I'm not actually sure what the problem is. I have recreated your example using the filter pane and a measure to filter the table based on the selection in the YearMonth unrelated table and it works perfectly:

The problem is that if you try doing the same with a card visual, you cannot seem to add a measure as a filter in the filter pane (as you can see in my example).

What is it that isn't working on your side?

[Anonymous]

@PaulDBrown 

Thanks for getting back with additional feedback. I've tried to use the Selection-Measure (please see screenshot below). However, the table doesnt include any result. Very strange, as the Selection-Measure should only filter year (in this case 2018). The slicer is linked to the DateFilter (copy of my orginial Date-table, with no relationships). There should be Amounts in all months during 2018.

Any idea of why it doesnt show any data?

[PaulDBrown]

@Anonymous 

The reason you are not seeing data in the "Cost account overview (actual month)" is because the table is lacking the Date filter context which is implicit in the measure you are using to filter the visual:

SelectedPeriod2 = IF(MAX(Date[Year]) = SELECTEDVALUE(DateFilter[Year]); 1;-1)

Convert the table to a matrix, and add your year field (from the date table) to the rows. This will allow you to see the values (you can then format the matrix to "hide" the year rows)

 Just beware that the values displayed will correspond to the period filtered. ie the whole year selected! - your table has "(actual month)" in the title; if you only want the actual month values, you will need to use a new measure in the filter pane which filters the date table for the actual month only, and not the whole year (in which case use the month-year field in the rows of the matrix instead of the year field to provide the filter context).

BTW, even though the simple card visual doesn't allow you to use measures as filters in the filter pane, the KPI visual does, so that is a workaround if you need it:

[PaulDBrown]

@Anonymous 

I like your solution to filtering the table using the filter pane: very easy and efficient.

As regards your quest to find an easy way to use your other measures, I am not aware of a way which does not involve writing new measures.

HOWEVER....

There may be a less onerous way, albeit it nevertheless still involves writing new measures...

There is an (arguably) slightly obscure FUNCTION in DAX which can come to the rescue for these kind of challenges; TREATAS.

This function (which was released relatively recently) in effect establishes a virtual relationship between two disconnected tables based on a common field. I have tested the function  against a couple of time intelligence functions working on a date table linked to a fact table and it "appears" to work smoothly. Here is an example:

The model looks like this:

Based on this, I have created a number of measures using the Date Table as the filter context:

For example:

MTD Actuals = CALCULATE([Sum Actuals]; 
               DATESMTD('Date Table'[date]))

or:

PYMTD Actuals = CALCULATE([MTD Actuals]; 
                DATEADD('Date Table'[date]; -1;YEAR))

or just a simple average, which responds to the Date Table filter context due to the relationship established in the model:

Average Actuals = AVERAGE('Fact Table'[Actuals])

We can then include these measures in a new CALCULATE function using the TREATAS function to filter using the unrelated period table:

(Following the order of the measures above)

MTD Actuals (TREATAS) = CALCULATE([MTD Actuals]; 
                        TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))

PYMTD Actuals (TREATAS) = CALCULATE([PYMTD Actuals]; 
                        TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))

Average Actuals (TREATAS) = CALCULATE([Average Actuals]; 
                        TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))

Which gives you this:

To conclude...

It doesn't solve your problem in the sense that you still have to write the measures. However, it does make life slightly easier in that you can write the new measures with a simple copy and paste + changing the target measure in the CALCULATE function.

New Measures  (TREATAS) = CALCULATE([Choose target measure];
                        TREATAS(VALUES('YearMonth Table'[Month Year]); 'Date Table'[Month Year]))

I hope it helps.

[amitchandak]

Have you tried edit interaction to solve this problem ?

[Anonymous]

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]

Could anyone help me on this issue? Would be great to better understand my options to get this to work

Thanks