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

How to create relative date filter using dax

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 

katvaldez_0-1602163107145.png

 

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!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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. 

amitchandak
Super User
Super User

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

 

 

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.

Top Solution Authors