cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katvaldez
Frequent Visitor

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 IV
Super User IV

@katvaldez , 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-3...

 

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.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
katvaldez
Frequent Visitor

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 IV
Super User IV

@katvaldez , 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-3...

 

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.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors