cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
9192gks
Helper III
Helper III

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

@9192gks 

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

@9192gks 

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

@9192gks 

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

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

@9192gks 

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

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 ?
 

@9192gks 

 

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

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

 

@9192gks 

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

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.

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

@9192gks 

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

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors