Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!