Forum Discussion
Solved
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...
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
ValtteriN
Community Champion
Community ChampionHi,
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:
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.)
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.