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
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.