Limiting slicer date table to fixed number of periods

[ritanoori]

Hello everyone 

I have this slicer date table in my PBI and what I'm trying to achive is limiting the display periods to 3. 

I received below comments in red with the pic to fix the issue but didn't know how to implement.  can you please help me. 

Thanks

Rita

1. This needs to go outside of your _Tbl variable. If you only require 3 months within your slicer, this should be added as part of your FILTER criteria
2. Your filter criteria needs to be updated to where you only return the StartDate and EndDate values
3. Since you’re bringing in Month (Date), you don’t need this. With points 4, 5, and 6, you also do not require these fields if this is just a simple slicer query.

DIM_SlicerDate =

var _maxdate = EOMONTH(TODAY(),0)

var _base = SELECTCOLUMNS(FILTER(ALLNOBLANKROW('Dim Date'[Month (Date)]), 'Dim Date'[Month (Date)] <= _maxdate), "Date", 'Dim Date'[Month (Date)])

var _tbl =

GENERATE(

_base,

var _Date = [Date]

var _Year = YEAR(_Date)

var _Quarter = FORMAT(_Date, "\QQ")

var _MonthNo = MONTH(_Date)

var _MonthText = FORMAT(_Date, "MM")

var _PerName = FORMAT(_Date, "MMM YYYY")

var _MonthYearNo = VALUE(_Year & _MonthText)

var _ModMonth = MOD(_MonthNo, 3)

var _MonthInQuarter = IF(_ModMonth = 0, 3, _ModMonth)

var _Set_PeriodsToDisplay = 3 // <- Change this to say how many periods you want to display

var _LastDate = _maxdate // <- Change to to restrict the range of dates to those that are 'Actual'

var _StartDate = EOMONTH(_LastDate, -3) // <- -1 is how many periods in the past. Change formula if using 1st day of month convention

var _EndDate = EOMONTH(_LastDate,+0) // <- Change formula if convention is to use 1st day of month

var _Result = FILTER(_base, _base >= _StartDate && _base <= _EndDate)

Return

ROW(

"Year", _Year,

"QtrYear", COMBINEVALUES("-", _Quarter, _Year),

"MonthNo", _MonthNo,

"MonthInQuarter", _MonthInQuarter,

"MonthYearNo", _MonthYearNo,

"Period", _PerName,

"Beg", EOMONTH(_Date, -1) + 1

))

return

_tbl

/*

1. I Don't want the Date Field

2. I only want my Month Date field

3. I want BEGINNING OF MONTH date

*/

[v-alq-msft]

Hi, @ritanoori 

You may try the following dax to see if it helps. You may replace the logic of 'Month Date' based on your requirement.

DIM_SlicerDate = 
VAR _maxdate =
    EOMONTH ( TODAY (), 0 )
VAR _base =
    SELECTCOLUMNS (
        FILTER (
            ALLNOBLANKROW ( 'Dim Date'[Date] ),
            'Dim Date'[Date] <= _maxdate
        ),
        "Date", 'Dim Date'[Date]
    )
VAR _tbl =
    GENERATE (
        _base,
        VAR _Date = [Date]
        VAR _Year =
            YEAR ( _Date )
        VAR _Quarter =
            FORMAT ( _Date, "\QQ" )
        VAR _MonthNo =
            MONTH ( _Date )
        VAR _MonthText =
            FORMAT ( _Date, "MM" )
        VAR _PerName =
            FORMAT ( _Date, "MMM YYYY" )
        VAR _MonthYearNo =
            VALUE ( _Year & _MonthText )
        VAR _ModMonth =
            MOD ( _MonthNo, 3 )
        VAR _MonthInQuarter =
            IF ( _ModMonth = 0, 3, _ModMonth )
        VAR _Set_PeriodsToDisplay = 3 // <- Change this to say how many periods you want to display
        VAR _LastDate = _maxdate // <- Change to to restrict the range of dates to those that are 'Actual'
        VAR _StartDate =
            EOMONTH ( _LastDate, -3 ) // <- -1 is how many periods in the past. Change formula if using 1st day of month convention
        VAR _EndDate =
            EOMONTH ( _LastDate, +0 ) // <- Change formula if convention is to use 1st day of month
        VAR _Result =
            FILTER ( _base, _base >= _StartDate && _base <= _EndDate )
        RETURN
            ROW (
                "Month Date",FORMAT([Date],"mmm-yyyy"),
                "Year", _Year,
                "QtrYear", COMBINEVALUES ( "-", _Quarter, _Year ),
                "MonthNo", _MonthNo,
                "MonthInQuarter", _MonthInQuarter,
                "MonthYearNo", _MonthYearNo,
                "Period", _PerName,
                "Beg", EOMONTH ( _Date, -1 ) + 1
            )
    )
RETURN
SELECTCOLUMNS(
    _tbl,
    "Month Date",[Month Date],
    "Year",[Year],
    "QtrYear",[QtrYear],
    "MonthNo",[MonthNo],
    "MonthInQuarter",[MonthInQuarter],
    "MonthYearNo",[MonthYearNo],
    "Period",[Period],
    "Beg",[Beg]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.