Forum Discussion

lucy_wilshaw's avatar
lucy_wilshaw
New Member
4 years ago
Solved

Rolling Date Measure for Power BI

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    lucy_wilshaw , Try like

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.