Rolling Date Measure for Power BI

[lucy_wilshaw]

Hi,

I would like to set up a new date measure in Power BI desktop using the DAX code. I currently have it set up to "Date = CALENDAR (DATE(2012,1,1), DATE(2020,12,31))" but would like to change it so it captures the latest 10 years data as I refresh the data. So I would ideally like to use a DAX code that calls data "from a specified date to a couple of months after view date" or the "latest 120 months of data from a specified date". I had a look at the DAX code list and thought EDATE or EOMONTH would do the job but I couldn't get them to work, I am afraid.

Many thanks for your help.

Best wishes,

Lucy

[Anonymous · Accepted solution]

Hi @lucy_wilshaw ,

I think you want to filter out the data of recent months from a starting date, the starting date and the number of months are both selected by the slicer.

Sample data

Here's my solution.

1.Create a calendar table.

Date = CALENDAR (DATE(2012,1,1), DATE(2020,12,31))

 There's no relationship between the calendar table and the main table.

2.Create a what-if parameter, set maximum is 120.

3.Create a measure. Put it into the page level visual and set show items when the value is 1.

Measure = var _start=SELECTEDVALUE('Date'[Date])
var _num=SELECTEDVALUE('Parameter'[Parameter])
return IF(MAX('Table'[Date])>=_start&&MAX('Table'[Date])<=EOMONTH(_start,_num-1),1)

Now you can filter date and month number.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lucy_wilshaw , Try like

Date =
var _end = date(year(today()),12,31)
var _st = date(year(today())-10,1,1)
return
calendar(_st,_end)