Default date doesn't roll over to the next month in a slicer

[powerbiuser444]

Hi all,

I created a calculated column to get the current date and put it in a slicer to have TODAY by default. Then I added other calculated columns with a month name and year. The problem is when the current date hits the next month it can't roll over and is stuck in the same month. So I have to pick another "Today" in the next month.

 

The logic in my columns is straightforward.

SlicerDate = IF([Date]=Today(),"Today",[Date]&"")

Month = FORMAT(DATE(1, MONTH([Date]), 1), "MMM")

Year = YEAR([Date])

Could you help to overcome that or should I use another logic?

[Greg_Deckler]

@powerbiuser444 Seems like this has to do about having a hierarchy in your slicer. If you look at the Data view for your table I imagine that you do not actually have any dates in July flagged with Today?

[powerbiuser444]

Hi @Greg_Deckler yes, it's true. July's 'Today' is empty, once I manually pick August's 'Today', July's 'Today' disappears. So this logic works only within a month, the 'Today' flag doesn't roll over to the next month.

[Greg_Deckler]

@powerbiuser444 That's what I figured. The issue is the use of the hierarchy in the slicer, not the DAX code or anything like that. If you didn't have the hierarchy, you wouldn't have the problem... 

[powerbiuser444]

@Greg_Deckler I understand that but how can I combine month and date? If I want to see the whole month selecting 30 days is not an option

[Greg_Deckler · Accepted solution]

@powerbiuser444 Use more than one slicer. One for year, one for month and one for date/today. Not ideal?

[powerbiuser444]

@Greg_Deckler if it's the only option then okay, not ideal but what can I do. Anyway thanks for your replies