Forum Discussion
Measure for consecutive days between two dates
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?
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
5 Replies
- ValtteriN
Community Champion
Hi,
Here is one way to do this:SupportTest =var _sdate = DATE(2021,7,1)var _edate = DATE(2021,12,31) returnIF(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.)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.
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.
smpa01jelledaems 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?
