Power Bi DAX: Relative Date filtering

[paulfink]

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.

[amitchandak · Accepted solution]

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