Date period slicer with custom range

[Anonymous]

I want to create a similar slicer like the below image in pbi 

I tried to create a table but i am unable to use today() and previousday isn't working

Date Periods =

UNION(

ADDCOLUMNS(

DATESMTD('Date'[Date]),

"Type", "Today",

"Sort", 1

),

ADDCOLUMNS(

DATEADD(DATESMTD('Date'[Date]),-1,DAY),

"Type", "Yesterday",

"Sort", 2

),

ADDCOLUMNS(

DATESYTD('Date'[Date]),

"Type", "YTD",

"Sort", 3

),

ADDCOLUMNS(

PREVIOUSMONTH(DATESMTD('Date'[Date])),

"Type", "Last Month",

"Sort", 4

),

ADDCOLUMNS(

PREVIOUSQUARTER(DATESQTD('Date'[Date])),

"Type", "Last Qtr",

"Sort", 5

),

ADDCOLUMNS(

PREVIOUSYEAR(DATESYTD('Date'[Date])),

"Type", "Last Year",

"Sort", 6

),

ADDCOLUMNS(

CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),

"Type", "All Time",

"Sort", 7

),

ADDCOLUMNS(

CALENDAR(MIN('Date'[Date]),MAX('Date'[Date])),

"Type", "Custom",

"Sort", 8

)

)

[Fowmy · Accepted solution]

@Anonymous 

Try the code as follows:

Date Periods = 
UNION (
    ADDCOLUMNS ( {TODAY()}, "Type", "Today", "Sort", 1 ),
    ADDCOLUMNS ( {TODAY()-1}, "Type", "Yesterday", "Sort", 2 ),
    ADDCOLUMNS ( DATESYTD ( 'Date'[Date] ), "Type", "YTD", "Sort", 3 ),
    ADDCOLUMNS ( PREVIOUSMONTH ( DATESMTD ( 'Date'[Date] ) ), "Type", "Last Month","Sort", 4),
    ADDCOLUMNS ( PREVIOUSQUARTER ( DATESQTD ( 'Date'[Date] ) ), "Type", "Last Qtr", "Sort", 5),
    ADDCOLUMNS ( PREVIOUSYEAR ( DATESYTD ( 'Date'[Date] ) ),"Type", "Last Year","Sort", 6),
    ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ),"Type", "All Time","Sort", 7),
    ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ), "Type", "Custom","Sort", 8)
)

[ianboothman84]

hi, the above is brilliant. Is there away to view this week and last week?

[Fowmy · Accepted solution]

@Anonymous 

Try the code as follows:

Date Periods = 
UNION (
    ADDCOLUMNS ( {TODAY()}, "Type", "Today", "Sort", 1 ),
    ADDCOLUMNS ( {TODAY()-1}, "Type", "Yesterday", "Sort", 2 ),
    ADDCOLUMNS ( DATESYTD ( 'Date'[Date] ), "Type", "YTD", "Sort", 3 ),
    ADDCOLUMNS ( PREVIOUSMONTH ( DATESMTD ( 'Date'[Date] ) ), "Type", "Last Month","Sort", 4),
    ADDCOLUMNS ( PREVIOUSQUARTER ( DATESQTD ( 'Date'[Date] ) ), "Type", "Last Qtr", "Sort", 5),
    ADDCOLUMNS ( PREVIOUSYEAR ( DATESYTD ( 'Date'[Date] ) ),"Type", "Last Year","Sort", 6),
    ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ),"Type", "All Time","Sort", 7),
    ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ), "Type", "Custom","Sort", 8)
)

[Anonymous]

Hey thanks ! Today() is working and yesterday works. But the previous month,current month and so on doesn't works.

I need something like

today,

yesterday,

7 days,

30 days,

current month,

previous month,

3 months

[Fowmy]

@Anonymous 

Please check the revised code below:

Date Periods = 
UNION (
    ADDCOLUMNS ( {TODAY()}, "Type", "Today", "Sort", 1 ),
    ADDCOLUMNS ( {TODAY()-1}, "Type", "Yesterday", "Sort", 2 ),
    ADDCOLUMNS ( DATESINPERIOD( 'Date'[Date] , TODAY(),-7,DAY ), "Type", "Last 7 Days", "Sort", 3 ),
    ADDCOLUMNS ( DATESINPERIOD( 'Date'[Date] , TODAY(),-30,DAY ), "Type", "Last 30 Days", "Sort", 4),
    ADDCOLUMNS (  DATESMTD('Date'[Date] ), "Type", "Current Month ", "Sort", 5),
    ADDCOLUMNS (  DATEADD(DATESMTD('Date'[Date]), -1,MONTH),"Type", "Previous Month","Sort", 6),
    ADDCOLUMNS ( DATESINPERIOD('Date'[Date], EOMONTH(TODAY(),-1), -3,MONTH),"Type", "Previous 3 Months","Sort", 7),
    ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ), "Type", "Custom","Sort", 8)
)

[Anonymous]

My Date table got only 1 date as of now, i.e 25/04/2021.

Previous month isn't working 

ADDCOLUMNS ( DATEADD(DATESMTD('Date'[Date]), -1,MONTH),"Type", "Previous Month","Sort", 6),

but when i use this 

ADDCOLUMNS ( DATEADD(DATESMTD('Date'[Date].[Date]), -1,MONTH),"Type", "Previous Month","Sort", 6),

I get previous month data of november 2021 which is incorrect. I need April month's data in previous month.

Do i need all year data in my Date table ?

[Fowmy]

@Anonymous 

Make sure your 'DATE' table has dates with complete years. 

DATE = CALENDAR ( "01/01/2021",

"31/12/2021")

Change the dates as you need but should be full years

[Anonymous]

It isn't working

I've already tried that and created relationship

But seems like it isn't working

[Fowmy]

@Anonymous 

Your original question was to create a table as per the logic you mentioned. I had no idea how you were going to use it in your model. However, If you set the CROSS FILTER direction to BOTH in your relationship, it should work but you should be well aware of the behavior in your measures and calculations.

[Anonymous]

Hi thanks for replying, Yes my original question was to create a table, In the pictures i have attached above, the previous and current months aren't working in table.

[Anonymous]

The cross filter worked thanks !!! I just need the Previous month,current month.I believe the DATESMTD isn't working.

[Fowmy · Accepted solution]

@Anonymous 

Please check the attached file it works for me I think.

[Anonymous]

Hey thanks for your pbi file, however it wasn't working for me.But i managed to change few queries and made it work, your reference worked really great!

Date Periods =
UNION (
ADDCOLUMNS ( {TODAY()}, "Type", "Today", "Sort", 1 ),
ADDCOLUMNS ( {TODAY()-1}, "Type", "Yesterday", "Sort", 2 ),
ADDCOLUMNS ( DATESINPERIOD( 'Date'[Date] , TODAY(),-7,DAY ), "Type", "Last 7 Days", "Sort", 3 ),
ADDCOLUMNS ( DATESINPERIOD( 'Date'[Date] , TODAY(),-30,DAY ), "Type", "Last 30 Days", "Sort", 4),
ADDCOLUMNS ( DATESINPERIOD('Date'[Date], EOMONTH(TODAY(),-1), 1,MONTH ), "Type", "Current Month ", "Sort", 5),
ADDCOLUMNS ( DATESINPERIOD('Date'[Date], EOMONTH(TODAY(),-1), -1,MONTH),"Type", "Previous Month","Sort", 6),
ADDCOLUMNS ( DATESINPERIOD('Date'[Date], EOMONTH(TODAY(),-1), -3,MONTH),"Type", "Previous 3 Months","Sort", 7),
ADDCOLUMNS ( CALENDAR ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) ), "Type", "Custom","Sort", 8)
)