Building A Current Month, Previous Month and YTD Slicer

[Phamacom]

Hi,

Thanks in advance. I have been searching the internet and came up short with a solution. I have searched all the youtube video but could not come up with an article or a video on building a slicer for current month, last month, year-to-date. I have a period table that contains a date column, which only shows the 1st day of each month from 2020 through 2025. For example, 1/1/20, 2/1/20, etc. I have 6 data tables with various types of information connecting to the Period table. All I want is to create a slicer that shows, Current Month, Previous Month, YTD. When I click on anyone of it, the information for my card, graph, etc.. will display based on my selection. Any help is appreciated. Thanks

Rob

[Alexrai · Accepted solution]

There are a few ways to do that... calculation groups are probably a better approach but they are more complicated and will require a proper calander table - this should work for a quick fix:

Create a single column table for slicing with the selections you want as text rows, e.g. YTD, This Month, Last Month.  Use SELECTEDVALUE to find the user selected slicer value (e.g. "This Month") and SWITCH(TRUE()) to determine the return value for the measure. In the example below we compare the Month & Year of Today() with the Month & Year of MaxDate and filter by first of the month per your requirement if those conditions are met, and if the switch fails it returns the sum as is it was a regular sum measure. The logic would be the same for other returns.

Sales Amount =

VAR SlicerSetting = SELECTEDVALUE(Slicer[Category])

VAR MaxDate = MAX(Data[Date])

RETURN

SWITCH(TRUE(),

SlicerSetting = "This Month", IF(AND(MONTH(MaxDate)=MONTH(TODAY()),YEAR(MaxDate)=YEAR(TODAY())),CALCULATE(SUM(Data[Sales]), Data[Date]=DATE(YEAR(TODAY()),MONTH(TODAY()),1)),BLANK()),

sum(Data[Sales])

)

In the context of a visual each date gets filtered to a single record so the max date is the same as the current point on the line graph or row in a matrix.

To get this to work in a card I think you could create a similar measure but drop the if statement since you don't need to return blanks.

[Phamacom]

Thanks Alexrai. I will give this try.

[VahidDM]

Hi @Phamacom 

Have you tried Bookmarks and buttons rarher than slicer?

Mastering Power BI Bookmarks

Power BI Bookmarks, Selections and Toggles

[Phamacom]

VahidDM,

Using Bookmark, Selection and Buttons will require me to do a monthly maintainence of changing the Current Month, Last Month, YTD. Please correct me if I'm wrong in my thininking as I have just got started with Power BI 3 days ago. That's why I prefer the DAX solution.