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,
I've been having a hard time trying to make a "procv like" in power BI.
I have this simple model with a Table with ID's and percentage values between dates and a Calendar (I use the portuguese version o PBI):
What i need is a measure that list the current value acording to the selected date at my calendar and the ID of the person, like a PROCV with TRUE parameter, so it can fill the empty cells with the closest value.
It has to be a measure and not a calculated column because the real model is way bigger than this.
I have created these 3 measures that get the closest result I need, but I just can't get the exactly result:
And this is the result I'm getting:
and the result I need:
I don't know if I was clear enough, but can someone help?
Solved! Go to Solution.
Hi,
In the Query Editor, write this formula and name the column as Date
={Number.From(date_start)..Number.From(date_end)}
Expand this column and then remove the start_date and end_date columns. Rebuild the relationship from the Date column of Table1 to the Date column of the Calendar Table.
To your visual, drag the Date column from the Calendar Table and write this measure
=MIN('Table1'[percentage])
Remember to drag date related fields such as Date/Month/Quarer etc. to your filters/slicers/visuals only from your Calendar Table.
Hope this helps.
Please refer to my blog on the current employee. I think in same formula if you use minx or maxx in place of countx , it should work. If not. If possible please share a sample pbix file after removing sensitive information.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi,
In the Query Editor, write this formula and name the column as Date
={Number.From(date_start)..Number.From(date_end)}
Expand this column and then remove the start_date and end_date columns. Rebuild the relationship from the Date column of Table1 to the Date column of the Calendar Table.
To your visual, drag the Date column from the Calendar Table and write this measure
=MIN('Table1'[percentage])
Remember to drag date related fields such as Date/Month/Quarer etc. to your filters/slicers/visuals only from your Calendar Table.
Hope this helps.
Thank you for the answer, Ashish.
I tried as you said, but it seems the formula ={Number.From(date_start)..Number.From(date_end)} doesn't work with open date ranges.
The ".." operator can't be aplied to a Number and Null fields.
You are welcome. Write this formula in the Query Editor and name the column as Date_end_final
=if date_end is null then DateTime.Date(DateTime.LocalNow()) else date_end
Now replace date_end, in my formula which i shared in the previous post, with date_end_final
Hope this helps.
Now it worked!
Instead of "DateTime.Date(DateTime.LocalNow()" I set to the end of 2020 to see the january/2020 result.
Then I used the MIN(Table[percentage]) to get me the current value and it shows as I need.
Thank you as well!
You are welcome.
Please refer to my blog on the current employee. I think in same formula if you use minx or maxx in place of countx , it should work. If not. If possible please share a sample pbix file after removing sensitive information.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
It worked!
I set the relationship to start and end to inactive and replace the COUNTX for MINX like this:
And it now shows as I needed.
Thank you.
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
123 | |
111 | |
78 | |
74 |