Search code examples
filterpowerbidax

PowerBI filter within 3 date ranges

I have a table with account number and 3 dates (balloon, maturity, and rate change). I want to filter these collectively where if ANY one field has a date within the next 36 months from a filtered Process Date that whole row gets returned. Using relative dates only returns rows fitting all 3 within the next 36 months.

I have come close with

filtcol = 

if(not(isblank(ds[BalloonDate])),(datediff(ds[AsOfDate], ds[BalloonDate], month) <=36), blank())
||
if(not(isblank(ds[MaturityDate])),(datediff(ds[AsOfDate], ds[MaturityDate], month) <=36), blank())
||
if(not(isblank(ds[ChangeDate])),(datediff(ds[AsOfDate], ds[ChangeDate], month) <=36), blank())

This correctly labels those rows with dates within 36 months as TRUE and those beyond as FALSE, but it also labels dates with past values as TRUE, what gives?enter image description here

Solution

  • DATEDIFF(D1, D2, X) will return a negative number if D2 is before D1. You will need to check if between 0 and 36.

    An example using VAR for local variables, and a SWITCH that may help with readability.

    filtcol = 
  var diffBallon = DATEDIFF(ds[AsOfDate], ds[BalloonDate], MONTH)
  var diffMaturity = DATEDIFF(ds[AsOfDate], ds[MaturityDate], MONTH)
  var diffChange = DATEDIFF(ds[AsOfDate], ds[ChangeDate], MONTH)

  return SWITCH( TRUE(),
    0 <= diffBallon && diffBallon <= 36, TRUE(),
    0 <= diffMaturity && diffMaturity <= 36, TRUE(),
    0 <= diffChange && diffChange <= 36, TRUE(),
    FALSE()
  )