Display only the month and year on Date Slicer

[Anonymous]

I need to display only the month and year on Date Slicer

I don't like to use timeline slicer available in market place.

I try to create two slicers from mOnth and to month 

My problem, the result are incorrects and how can I control when the user selects from month > to month?

Here is a pbix file https://drive.google.com/file/d/1IC8FP7vKBpjrm92gr2yV_CgbhwYOeMJb/view?usp=drivesdk

[VjekoL]

Great I've found this post. I was having the same issue. 

However, Time Intelligence Functions like "Sameperiodlastyear" don't seem to work properly that way. To be more precise: 
Filtering for example from Jan 2020 - Dec 2020 won't be able to show Values from Jan 2019 - Dec 2019 using Calculate and Sameperiodlastyear, because the 'From Month' and 'To Month' Tables filter the 'Date Table' to just include 2020 in this particular case, thus 2019 can't be passed on to the Table with the Values in it. 

Is there a workaround for this? Or am I missing something here? 😄 @OwenAuger 

Regards,

Vjeko

[OwenAuger]

Hi @VjekoL 

Good point - this is a bit of a downside with this particular method.

The reason that this setup doesn't work as expected with time intelligence functions is that we are filtering on tables 'From Month' and 'To Month' that are not part of the expanded 'Date' table.

When any filters are applied to 'Date'[Date] by time intelligence functions or otherwise, all existing filters on the expanded 'Date' table are cleared with an automatic ALL ( 'Date' ).

The expanded 'Date' table includes all tables that are on the 1-side of a many-to-1 relationship from the 'Date' table (directly or indirectly). However 'From Month' and 'To Month' are not on the 1-side of an indirect relationship to 'Date'.

In order to make time intelligence functions or other date filter-modifying expressions work, we would need to add ALLCROSSFILTERED ( 'Date' ) as a modifier. 

Quoting dax.guide:

ALLCROSSFILTERED removes all the filters on an expanded table (like ALL) and on columns and tables that are cross-filtering the table argument because of bidirectional cross-filters set on relationships directly or indirectly connected to the expanded table.

This would mean you would have to write measures like this:

MyMeasure Last Year = 
CALCULATE ( 
    [MyMeasure],
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    ALLCROSSFILTERED ( 'Date' )
)

A bit of an annoyance, but you could write the logic once per time intellgence calc with Calculation Groups.

Regards,

Owen

[VjekoL]

I just tried it and it works perfectly. The solution is much easier than i expected it to be 😁

Thank you for the explanation. Makes it so much clearer to me

[OwenAuger · Accepted solution]

Hi @Anonymous 

Here's one suggestion (PBIX attached):

1. Define some additional tables as follows (done in DAX here but can equally be done in Power Query):

Year-Month = 
ALLNOBLANKROW ( 'Date'[Year-Mon] )

From Month = 
GENERATE ( 
    SELECTCOLUMNS ( 'Year-Month', "From Year-Month", 'Year-Month'[Year-Mon] ),
    FILTER ( ALLNOBLANKROW ( 'Year-Month'[Year-Mon] ), 'Year-Month'[Year-Mon] >= [From Year-Month] )
)

To Month = 
GENERATE ( 
    SELECTCOLUMNS ( 'Year-Month', "To Year-Month", 'Year-Month'[Year-Mon] ),
    FILTER ( ALLNOBLANKROW ( 'Year-Month'[Year-Mon] ), 'Year-Month'[Year-Mon] <= [To Year-Month] )
)

The gist of these tables is is:

• Year-Month contains distinct Year-Month values
• From Month contains a column [From Year-Month] which contains all distinct Year-Month values, joined to a column [Year-Mon] containing all months >= [From Year-Month]
• To Month contains a column [To Year-Month] which contains all distinct Year-Month values, joined to a column [Year-Mon] containing all months <= [To Year-Month]

2. Then create relationships as follows (bidirectional relationships between From Month / To Month & Year-Month):

3. Finally create slicers on 'From Month'[From Year-Month] and 'To Month'[To Year-Month] similar to your original slicers.

4. Optionally, create measures to use as visual level filters on the two slicers, to ensure dates outside the overall date range are not displayed.

The result of this setup is that:

• From Month and To Month filter each other in an intuitive way, by virtue of the bidirectional relationships. For example 'To Month' filters 'Year-Month' which filters 'From Month'
• The filters from the two slicers combine to filter Year-Month and therefore 'Date' and 'Table (2)' in this case.

Does this solve your issue?

Regards,

Owen

[Anonymous]

It works exactly as it's needed. Awesome! Thank you very much. 

[amitchandak]

@Anonymous , for this one you need two independent tables. And create a measure in to table like this

countx(filter(Date2, Date[Date] >selectedvalue(Date1[Date]),Date2[Date]) and at visual level check this not blank

Approch of month year sclicer should be lik this blog

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601