topic Re: How to create relative date filter using dax in DAX Commands and Tips

How to create relative date filter using dax

Anonymous — Thu, 08 Oct 2020 14:32:27 GMT

Hi everyone,

I am trying to create a measure to filter my graph based on relative dates. but my measure is not working unfortunately

My default graph looks like

I'd like that if i click Previous Months button, it should display all past data until end of last month (September 30 2020).

IF current month is Nov 2020, i should see all past data until end of last month (oct 31 2020) and so on.

and if i click the Current Month -> button, it should only display values from current month (from Oct 1 2020) until the max date with value. if current month = December, i should see all data from dec 1 2020 until the max date with value.

i have a calendar table (Date) that is linked to my transactions table (Client Ddate). what i did is i created a calculated column inside Calendar table:

TodayDate =

var today = FORMAT(TODAY(), "mm/YYYY")

return

DATEDIFF(today,'Calendar'[Date],month)

then created measures to filter the past dates and current + future dates

Past Date =CALCULATE('Transaction'[A.Value], FILTER('Calendar', 'Calendar'[TodayDate] < 0))

CurrentFuture =CALCULATE('Transaction'[A.Value], FILTER('Calendar', 'Calendar'[TodayDate] = 0 || 'Calendar'[TodayDate] > 0))

PROBLEM: the measures are not filtering the graph when used in switch formula bec i think my measures might be incorrect in the first place

I hope you can help me with this. Thank you so much in advance!

Re: How to create relative date filter using dax

amitchandak — Thu, 08 Oct 2020 15:47:08 GMT

Anonymous , refer if this old solution can be bit of help

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629

For this month vs last month use time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Re: How to create relative date filter using dax

Anonymous — Fri, 08 Jan 2021 16:34:26 GMT

Hi amitchandak really sorry i totally forgot to respond to you. Anyway, thank you so much for your solution. it worked perfectly. I appreciate it very very much.

Re: How to create relative date filter using dax

hsantosh — Fri, 21 Jun 2024 11:57:25 GMT

i am using relative date slicer
same i want to display in other card visual start date and end date from that slicer