Power BI: Advance date periods slicer

Here is a better idea to take date slicer into the next level simply with DAX queries,

You can select the previous year, previous month, previous quarter, month till date, quarter till date, year till date, yesterday, previous week & last n no. of days data.

DatePeriods =

VAR _datetable = DateTable

VAR _today = TODAY()

VAR _month = MONTH(TODAY())

VAR _year = YEAR(TODAY())

VAR _thismonthstart = DATE(_year,_month,1)

VAR _thisyearstart = DATE(_year,1,1)

VAR _lastmonthstart = EDATE(_thismonthstart,-1)

VAR _lastmonthend = _thismonthstart-1

VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)

RETURN UNION(

ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),”Type”,”|Pervious Day”,”Sort”,6

),

ADDCOLUMNS(PREVIOUSYEAR(DATESYTD(‘Date’[Date])),

“Period”, “|Previous Year”, “sort”, 5

),

ADDCOLUMNS(PREVIOUSMONTH(DATESMTD(‘Date’[Date])),

“Period”, “|Previous Month”, “Order”, 3

),

ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),

“Period”, “|Last 7 Days”, “Order”,2

),

ADDCOLUMNS(PREVIOUSQUARTER(DATESQTD(‘Date’[Date])),

“Period”, “|Previous Quarter”, “Order”, 4))

You can also add different date functions as per requirements.

Thank you

--

--

--

As we are freshers, we have theoretical knowledge, but we can do hard work. We will put all our efforts and creativity into good progress. (0 to 100)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

WeChat Mini Programs the future of Mobile in China, and the World?

Securing Your Container Workloads in Kubernetes

How to Handle your Python packaging in Lambda with Serverless plugins

box-tokener: A Node.js Command Line Tool to Generate Box API Tokens

HOW TO ACCESS THE CPANEL ON BLUEHOST

Single, Double, and Triple Quotes in Python

Software Testing Hot Cakes for 2018

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tanay Deshmukh

Tanay Deshmukh

As we are freshers, we have theoretical knowledge, but we can do hard work. We will put all our efforts and creativity into good progress. (0 to 100)

More from Medium

Problem data

Save Historical Data in the Power BI Service with XMLA Endpoint (Incremental Refresh on ANY Source!)

Story-Telling with Data

How to use “Esquisse” in VS Code to create visualizations