Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filtering on Date complex

Hi,   I have a "Date" dimension and another table in which my data looks like :-   WorkID StartDate EndDate Type 1 1/1/2014 1/5/2017 Rejected 2 7/7/2015   Active 3 4/4/2016 ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    basically what you are looking for is called the "Event in Progress".

     

    Please be aware, that solutions for these kind of questions require that the Calendar table is not related to the fact table. 

     

    Here you will find a pbix file that contains a little example, based on your sample data it shows 3 rows valie for the selected month "2016-06":

    I've created a measure "Active WorkID"

    Active WorkID = 
SUMX(
    'Fact'
    ,
    var theEnd = IF(ISBLANK('Fact'[EndDate]), DATE(9999,12,31), 'Fact'[EndDate])
    return
    IF(AND('Fact'[StartDate] < [Min Selected Date], theEnd > [Max Selected Date])
        ,1
        ,BLANK()
    )
)

    that I'm using with the card visual. this measure returns the value 1 one for each WorkID that is "active". This measure can also be used in the Visual level filter section of a visual.

     

    Hope this is what you are looking for

     

    Regards

    Tom

  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    so you are saying 

    Month('startdate' + 45) = Month(Selected)

    Left side

    2014-07-01 + 45days = 2014-08-15 --> Month = 8

    Right side

    2018-08 (Selected Month) = Month(...) = 8

    this comparison equals to true?

     

    Not sure what you mean if you are saying you can't see trends, trends of what

    I would consider this a trend.

     

    I have to admit that I have my difficulties to understand your requirements, sorry for that

     

    Regards

    Tom

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

can you please provide examples assuming that 2016-05 and 2016-09 is selected in the sliccer. I still do not understand what you mean by

createddate+45 (the month) = currentmonth (selected in filter with ability to select multiple months)

Regards

Tom

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I mean that the pbix you have only allows me to choose a month & year. How can I see trend in this case? Also,

 

createddate+45 (the month) = currentmonth (selected in filter with ability to select multiple months)

This means Month(startdate+45) = month(the month in visual). 

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    so you are saying 

    Month('startdate' + 45) = Month(Selected)

    Left side

    2014-07-01 + 45days = 2014-08-15 --> Month = 8

    Right side

    2018-08 (Selected Month) = Month(...) = 8

    this comparison equals to true?

     

    Not sure what you mean if you are saying you can't see trends, trends of what

    I would consider this a trend.

     

    I have to admit that I have my difficulties to understand your requirements, sorry for that

     

    Regards

    Tom