Current Value Between Date Ranges

[JulioSTJ]

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?

[Ashish_Mathur · Accepted 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.

[Ashish_Mathur · Accepted 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.

[JulioSTJ]

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.

[Ashish_Mathur]

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.

[JulioSTJ]

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!

[Ashish_Mathur]

You are welcome.

[amitchandak · Accepted solution]

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.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

[JulioSTJ]

It worked!

I set the relationship to start and end to inactive and replace the COUNTX for MINX like this:

Current Percentage = CALCULATE(MINX(FILTER(Table1;Table1[date_start]<=max('Calendar'[Date]) && (ISBLANK(Table1[date_end])

|| Table1[date_end]>max('Calendar'[Date])));(Table1[percentage])))

And it now shows as I needed.

Thank you.