Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Date period slicer with custom range

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

9192gks_0-1622270082232.png

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
)
)

 

2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@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)
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@Anonymous 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

12 REPLIES 12
ianboothman84
Helper II
Helper II

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

Fowmy
Super User
Super User

@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)
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

@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)
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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 ?
 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

It isn't working

9192gks_2-1622301836108.png

 

I've already tried that and created relationship

9192gks_0-1622301636107.png

But seems like it isn't working

9192gks_1-1622301751729.png

 

@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.

Fowmy_0-1622302967875.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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
Not applicable

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

@Anonymous 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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)
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.