Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 @EricHulshof,
If I understand you correctly, SELECTVALUE() function might be helpful for you.
Please check following measure and see if the result achieve your expectation:
Measure =
var sv = RIGHT(SELECTEDVALUE('Date'[Date]),4)
return
IF(RIGHT(MAX('Table'[Active from]),4)<=sv&&RIGHT(MAX('Table'[Active till]),4)>=sv,1,0)
The Date column in my sample data is Text type, you can use FORMAT() function to change type if it is Date type in your data.
Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EricHulshof,
If I understand you correctly, SELECTVALUE() function might be helpful for you.
Please check following measure and see if the result achieve your expectation:
Measure =
var sv = RIGHT(SELECTEDVALUE('Date'[Date]),4)
return
IF(RIGHT(MAX('Table'[Active from]),4)<=sv&&RIGHT(MAX('Table'[Active till]),4)>=sv,1,0)
The Date column in my sample data is Text type, you can use FORMAT() function to change type if it is Date type in your data.
Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Allright!
i knew about the selectedvalue function, i just couldnt figure out how to properly use the column values. Max and Right where the things i was looking for!
Thanks alot!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |