cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulfink Helper I
Helper I

Power Bi DAX: Relative Date filtering

Hi guys,

 

I have a quick question, is there a way to filter data from set days between 2 months? E.g filter from 16-15 of Jan-Feb.

Is this possible?

 

For example i have used a measure to calculate days between dates - Last Assessment 16-15 = CALCULATE(SUM('Table1'[Duration1]),'Table1'[Start], DATESBETWEEN('Calendar'[Date], [Assessment Date], [Assessment one month]))

  • Assessment Date = if(DAY(TODAY())<16,DATE(YEAR(TODAY()),MONTH(TODAY())-1,15),DATE(YEAR(TODAY()),MONTH(TODAY()),15))
  • Assessment one month = EDATE([Assessment Date],-1)+1
  • Assessment 6 = EDATE([Assessment Date],-6)+1
  • Assessment 12 = EDATE([Assessment Date],-12)+1

The last assessment does show from the 16th of 2 months ago to last months 15th e.g Dec 16th - Jan 15th. But i need to show from last 6 months and the last 1 year.

 

How can i work this out so i can show the Last 6 months and 1 year.

 

So far i have had to use a date filter to manually select the dates which i want to stop and have it be automatic.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Power Bi DAX: Relative Date filtering

Assume you have date table. And you want to start from 15 of this month

Rolling 1 = 
var _date  = date(year(today),month(today),15)
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_date,-1,MONTH))  

 

15th of last month

Rolling 1 = 
var _date  = date(year(today),month(today)-1,15)
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_date,-1,MONTH))  

Even if subtract month more than(in _date ) 12 it will adjust the year.

You can change no of the month in datesinperiod to get more months

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

1 REPLY 1
Super User IV
Super User IV

Re: Power Bi DAX: Relative Date filtering

Assume you have date table. And you want to start from 15 of this month

Rolling 1 = 
var _date  = date(year(today),month(today),15)
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_date,-1,MONTH))  

 

15th of last month

Rolling 1 = 
var _date  = date(year(today),month(today)-1,15)
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_date,-1,MONTH))  

Even if subtract month more than(in _date ) 12 it will adjust the year.

You can change no of the month in datesinperiod to get more months

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors