Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
@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
@jelledaems did you try this out yet?
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.
Hi,
Here is one way to do this:
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!
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |