Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
michaelho
Frequent Visitor

If date is between 2 dates from another table return value

I have a DateTable with a list of dates and then I have the table below with fiscal year data:

 

michaelho_0-1667252300385.png

 

I'm trying to add columns to the DateTable to reflect the data from this fiscal year table. For the Financial Period I was able to use the below formula and it worked great:

 

 

Financial Period w/ Dates =
MINX(
    FILTER(
        'Fiscal Calendar Data',
        'Fiscal Calendar Data'[Start Date]<=DateDim[Date] &&
        'Fiscal Calendar Data'[End Date]>=DateDim[Date]),
        'Fiscal Calendar Data'[FY Period Dates])

 

 

However when I try to use the same formula for the "Ship Days", the data returned is not correct (for P12 it returns the correct value of 20 but for all the rest of the dates it returns 19).

 

I've tried many other suggestions (including this one) but I continue to get the error message: A table of multiple values was supplied where a single value was expected.

 

Anyone have any other suggestions for how to solve this?

1 ACCEPTED SOLUTION

@michaelho 

pls see the attachment to check if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
briankennemer
New Member

I know this thread is old but I think your formula is correct but your dataset is wrong. Periods 11 and 12 are both 13 months long. The from dates for 11 and 12 are both in 22 instead of 23. 

michaelho
Frequent Visitor

@ryan_mayu Maybe you can make sense of this - I literally copied and pasted the same fiscal calendar data into your sample file (power query) as a new data source and used the same formulas you did, replacing your fiscal calendar table column with the new ones, and I'm getting different results:

 

returnvalue.v2 

michaelho
Frequent Visitor

@ryan_mayu  It should be noted that the Fiscal Calendar data is coming from a shared Google Sheets doc so that it can be easily accessible for others in the company to update whne needed. 

 

I even tried connecting your sample file to the Google Sheet I'm using for the Fiscal Calendar and I'm having the same issues in your file when I reproduce your formulas with the Google Sheet fiscal calendar. Leads me to believe this has something to do with the originating file.

 

Finally, I tried using an Excel file for the Fiscal Calendar data instead and I'm still getting the same incorrect results. Perplexing.

michaelho
Frequent Visitor

Thank you, @ryan_mayu! It looks good in your example. However, what's strange is when I add that formula into my pbix file it's not producing the same results. Leads me to believe there's something else going on in my file that's affecting this.

michaelho
Frequent Visitor

Hi @ryan_mayu,

 

I'm trying to take a date table with a list of dates:

michaelho_0-1667317930627.png

 

And a fiscal calendar with various data points like the number of ship days in each period:

 

Fiscal YearPeriodFY PeriodFY Period DatesStart DateEnd DateShip Days
FY23P01FY23 P01FY23 P01 (2/28-4/3)2/28/20224/3/202225
FY23P02FY23 P02FY23 P02 (4/4-5/1)4/4/20225/1/202220
FY23P03FY23 P03FY23 P03 (5/2-5/29)5/2/20225/29/202220
FY23P04FY23 P04FY23 P04 (5/30-7/3)5/30/20227/3/202224
FY23P05FY23 P05FY23 P05 (7/4-7/31)7/4/20227/31/202219
FY23P06FY23 P06FY23 P06 (8/1-8/28)8/1/20228/28/202220
FY23P07FY23 P07FY23 P07 (8/29-10/2)8/29/202210/2/202224
FY23P08FY23 P08FY23 P08 (10/3-10/30)10/3/202210/30/202220
FY23P09FY23 P09FY23 P09 (10/31-11/27)10/31/202211/27/202219
FY23P10FY23 P10FY23 P10 (11/28-1/1)11/28/20221/1/202324
FY23P11FY23 P11FY23 P11 (1/2-1/29)1/2/20221/29/202319
FY23P12FY23 P12FY23 P12 (1/30-2/27)1/30/20222/27/202320

 

And combine them so the data from the fiscal calendar is added to the date table based on the Date (column A of the Date table) falling between the Start and End dates in the Fiscal Calendar:

michaelho_2-1667318085740.png

 

@michaelho 

pls see the attachment to check if this is what you want





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@michaelho 

could you pls provide the sample data for both table and the expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors