Calculate Period of Date field that is in between two columns of date/time fields

[mack0352]

Hello! 

I table that contains a Datetime field "Quote Sent Date" which includes ~30000 rows. Some of these records and timestamps may show up more than once (our company may send out some quotes at the same time). 

Query1: 

Quote ID             Quote Sent Date                                Period?

xxxx-xxxxxx          10/2/2016 5:10:00 PM                        

What I need to do within PowerBi Desktop, is determine which Fiscal Period that that quote was sent to our customer. It is pretty simple to do in Excel with a VLookup, but, I cannot figure out how to do this in PowerBi. 

I have a separate table which includes FY15, FY16, and FY17 date/time fields to relate the beginning and ends of the periods. 

Sheet1

Period                       Start Date                                            Finish Date 

FY16 P01                   10/1/2015 12:00:00 AM                       10/25/2015 11:59:59 PM 

Any ideas or suggestions? 

Thanks so much! 

[CahabaData · Accepted solution]

well you could consider the brute force method of making an actual record for every day in your Fiscal Year Table... right now you just have a record per period and bracketed start/end date fields....

this would mean you would have 365 records per year....  but then you can join on the date field between the 2 tables and thereby reference the related Period value.

Getting all dates between 2 dates.....actually as I type this it seems I thought I saw that this was now an embedded feature of the product - sorry a little foggy on this point at the moment......

[Framet]

Hi 

Have you considered a calculated column that is added to your data table?

Maybe LookupValue function can help.

https://msdn.microsoft.com/en-us/library/gg492170.aspx

There are ways you could do this with a DAX measure for sure but it would be hard for me to advise without more details of the relationships you have or will need. Others may give you more direction.

Just a note that unless time part of the date is important you may find many formula are more efficient especially on large data if the dates are loaded in as date only.

Cheers

Thomas

[mack0352]

Thanks! 

I did research the LookupValue function and played around with it a little bit, but I wasn't successful. Since that fuction was literally looking for exact dates, it wasn't using the "Range" between the. I think you are right though, I probably should get rid of the "Time' aspect of may "Quote Sent" column, and potentially just create table with all 365 days and their corresponding period. Then, the LookUpValue function may work out. 

I'll take a look on Monday and let you know how it turns out. 

[Framet]

Good plan and it is good practice to use a calendar table. If your periods are set based on some logic you could even let PowerBI create the calendar table for you and write an additional column on the calendar table that calculates the period any day falls on.

Regardless of how you create your date table, if you can remove the time part from your dates then join the data you have to the calendar table and filtering by period will function as is and if you want a calculates column on the original table you can use =related(yourdatetable[yourperiodcolumn])