Measure for consecutive days between two dates

[jelledaems]

Hi everybody,

My best wishes for the New Year!

I have a table that contains which people get what kind of support from the government:

Person_Name	Type_Support	From_Date	To_Date
Joe W.	money	2021-06-01	2022-02-01
Jeff	money	2021-06-01	2021-11-29
Chris	house	2019-01-01	2022-12-31
Joe W.	money	2021-06-01	2021-08-01
Joe W.	house	2020-01-01	2022-12-31

Now I would like a way to get a list of all the Person_Name that have had Type_Support money from 2021-07-01 until at least 2021-12-31 without missing one day of support in that period. In the example Jeff would not come out of that list because he has only support until november. Joe W. will come out of that list (attention: he even has two support of money in that period at the same time)

I am a creator of reports, but I cannot change the dataset. This means that I would like to do this using a measure, so not using Power Query and preferably not adding calculated columns.

I have tried some things, but I'm not so good in DAX, so I got stuck. Is there anybody that cna put me on my way how to solve this?

[smpa01 · Accepted solution]

@jelledaems  you can create a measure like this

Measure =
VAR _min =
    MIN ( 'Calendar'[Date] )
VAR _max =
    MAX ( 'Calendar'[Date] )
VAR _name =
    CALCULATE (
        MAX ( 'Table 1'[Person_Name] ),
        FILTER (
            'Table 1',
            'Table 1'[From_Date] <= _min
                && 'Table 1'[To_Date] >= _max
                && 'Table 1'[Type_Support] = "money"
        )
    )
RETURN
    _name

[smpa01]

@jelledaems  did you try this out yet?

[jelledaems]

 My idea was to first calculate a variable in the measure that will make a list of all dates between 2021-07-01 until 2021-12-31, which is easy to do with the CALENDAR function. 

But then I would also need a list of all dates for which that person has support of money and then use the EXCEPT function to see if there are any dates without support or something... I don't know how to do something like that, so it is hard to explain.

[ValtteriN]

Hi,

Here is one way to do this:

SupportTest =

var _sdate = DATE(2021,7,1)

var _edate = DATE(2021,12,31) return

IF(SELECTEDVALUE('Table (3)'[Type_Support])="money"&&SELECTEDVALUE('Table (3)'[From_Date])<=_sdate&&SELECTEDVALUE('Table (3)'[To_Date])>=_edate,1,0)

Example in table:

Now we can use this measure to filter the table to get the names:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Disclaimer, If you want to construct the period from multiple rows (e.g. Joe could have first stupport from 1.7.2021 to 1.8.2021 and then 1.8.2021 to 1.1.2022 things will get more complicated.)

[jelledaems]

Hi, thank you for your quick reply. The only thing is - and that might be my fault for not mentioning it - but you can have monthly money support as well, for example:

Joe W.	money	2021-06-01	2021-07-01
Joe W.	money	2021-07-01	2021-08-02
Joe W.	money	2021-08-02	2021-10-15
Joe W.	money	2021-10-15	2022-02-02

In this example Joe W. should also come out of this list, because he has not interrupted money support from 2021-07-01 until at least 2021-12-31 - even though it is in mutiple records. And that is actually the thing I am struggling with. Sorry for not making this more clear in my previous example.