The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone. I have a dataset looking like this:
Date ordered:
Date: |
1-1-2012 |
5-6-2013 |
6-8-2014 |
This tabel has lots of dates. I use the YEAR value of this table to select a year.
Next i have a table with:
Number | Name | Active from | Active till |
111 | Name1 | 1-1-2012 | 31-12-2100 |
222 | Name2 | 1-1-2012 | 31-12-2012 |
333 | Name3 | 1-1-2012 | 31-12-2013 |
4556 | Name4 | 1-1-2014 | 31-12-2100 |
5775 | Name5 | 1-1-2014 | 31-12-2100 |
6574 | Name6 | 1-1-2014 | 31-12-2100 |
87544 | name7 | 1-1-2014 | 31-12-2100 |
So now i want to create a field, showing wich number was active at the selected date.
Example i select 2013. this should show up.
Number | Name | Active from | Active till | WasActive |
111 | Name1 | 1-1-2012 | 31-12-2100 | 1 |
222 | Name2 | 1-1-2012 | 31-12-2012 | 0 |
333 | Name3 | 1-1-2012 | 31-12-2013 | 1 |
4556 | Name4 | 1-1-2014 | 31-12-2100 | 1 |
5775 | Name5 | 1-1-2014 | 31-12-2100 | 1 |
6574 | Name6 | 1-1-2014 | 31-12-2100 | 1 |
87544 | name7 | 1-1-2014 | 31-12-2100 | 1 |
I tried doing this with a measure, but i cannot use columns in a measure. The i tried it using a calculated column, but i can not use selectedvalue in a calculated column.
The calculated column looks like this:
WasActive = IF(AND([Selectedvalue] >= Table2[Active from].[Year];[Selectedvalue] <= table2[Active till].[Year]);1;0)
Selectedvalue = 2013
Solved! Go to Solution.
Hi @Anonymous ,
You need to create a measure if you want dynamic values.
Measure =
VAR a =
YEAR ( SELECTEDVALUE ( 'Table'[Active from] ) )
VAR b =
YEAR ( SELECTEDVALUE ( 'Table'[Active till] ) )
RETURN
IF (
SELECTEDVALUE ( 'Table (2)'[Year] ) >= a
&& SELECTEDVALUE ( 'Table (2)'[Year] ) <= b,
1,
0
)
Here is the result.
Here is my test file for your reference.
Hi @Anonymous ,
You need to create a measure if you want dynamic values.
Measure =
VAR a =
YEAR ( SELECTEDVALUE ( 'Table'[Active from] ) )
VAR b =
YEAR ( SELECTEDVALUE ( 'Table'[Active till] ) )
RETURN
IF (
SELECTEDVALUE ( 'Table (2)'[Year] ) >= a
&& SELECTEDVALUE ( 'Table (2)'[Year] ) <= b,
1,
0
)
Here is the result.
Here is my test file for your reference.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |