Date Slicer with DAX calculation

[Anonymous]

Hi,

i have a date table containg many dates (incremantaly).

i wish to create a culomn that will allow me to create a slicer with the current groups.

for the current month( MAR-2020) it will show "This Month"

for the FEB -2020 it will Show "Show Last Month"

for the 6 month before FEB -2020 show in the last 6 month before last month(FEB-2020)"

for the months : Jan-2020,Dec-2019,Nov-2019,Oct-2019 ,Sep-2019,Aug-2019 :in the last 6 month before last month(FEB-2020)"

see below example:

Date(from Date table)(DD-MM-YY)	Month&Year(MM-YY)	Group
1/3/2020	Mar-20	Current Month
1/2/2020	Feb-20	Last month
1/1/2020	Jan-20	In the last 6 Months
1/12/2019	Dec-19	In the last 6 Months
1/11/2019	Nov-19	In the last 6 Months
1/10/2019	Oct-19	In the last 6 Months
1/9/2019	Sep-19	In the last 6 Months
1/8/2019	Aug-19	In the last 6 Months
1/7/2019	Jul-19	Other months
1/6/2019	Jun-19	Other months
1/5/2019	May-19	Other months

thanks!

[Anonymous]

why do want like this column?

There is already feature "relative date" for date slicers where you can select today last N month/Year/Days.

Make your slicer as relative date slicer.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[Anonymous]

hi,

the reason i want is as a coulmn since the costumer want to have a slicer botton on the report that will able him to filter the page for each period

current month

previus month

last 6 months before previus month

thanks

[Anonymous]

one solution is here.

https://community.powerbi.com/t5/Desktop/Date-Selector-interaction-with-Date-Slicer/m-p/963412#M4616...

but when you mark you date slicer as relative it will allow your end user to select particular time span like last 6 months,today, this week.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Mariusz]

Hi @Anonymous 

The below blog post explains how to achieve this with use of query editor, it returns 0 for the current month and -1 for previous and so on, later you can add a column converting numbers to text.

https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn