Forum Discussion
DATESINPERIOD multiple values supplied?
i am trying to create a list for each row with dates incremented by 1 month but not exceeding period_end date. I am currently trying to use the below formula but am getting "A Table of multiple values was supplied where a single value was expected" is there a way i can make it so it just duplicates the row for each date between?
Invoice Dates = DATESINPERIOD('Date'[Date],'Rev by Territory (2)'[Adjusted Added When],'Rev by Territory (2)'[Qty],MONTH)
8 Replies
Hi@ zombieug
After my research , you may try to do these like below:
User Crossjoin Function to create a table like this
Table = FILTER(CROSSJOIN(Table1,'Date'),DATEDIFF('Date'[Date],Table1[endate],DAY)<=30&&DATEDIFF('Date'[Date],Table1[endate],DAY)>0)Then filter table
Result:
If it is not your case,Please share some data sample and the expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
hi,@dataman123
After my research,you can do these as below:
Basic data
use this formula
New Invoice Dates = FILTER(CROSSJOIN('Table1 2','Date'),DATEDIFF('Date'[Date],'Table1 2'[Added Date],DAY)<=30&&DATEDIFF('Date'[Date],'Table1 2'[Added Date],DAY)>0&&'Table1 2'[Added Date]<='Table1 2'[End Date])Result:
By the way, I have tested on my side, and reproduce the issue.
it can be achieved , you need to use Crossjoin function.
Best Regards,
Lin
HI,@dataman123
You can try to use this measure
Invoice Dates = FILTER(CROSSJOIN('Table11','Date'),DATEADD('Date'[Date],1,MONTH)>Table11[Added Date]&&'Date'[Date]<=Table11[Added Date]&&'Table11'[Added Date]<='Table11'[End Date])Result:
here is demo, please try it .
https://www.dropbox.com/s/xtynyqkzffzskc6/DATESINPERIOD%20multiple%20values%20supplied.pbix?dl=0
Best Regards,
Lin
thank you for your help, 1 issue with this is the new date column should only last the number of months so in your example order ID 22681 should only last 5 months so the dates should be start date 25/03/18 then 25/04/18 then 25/05/18 then 25/06/18 then 25/07/18 and the last date would be 25/08/18. As i am looking to see the date the order needs to be invoiced each month
v-lili6-msft this currently will give me the number of days in between the start and end date but i need months;
Table 2 =
SELECTCOLUMNS(
GENERATE(
'Table1',
FILTER(
CALENDAR(MIN('Table1'[Added Date]),MAX(Table1[End Date]))
,[Date]>=[Added Date] && [Date] <= IF(NOT IsBlank([End Date]),[End Date],MAX('Table1'[End Date])
)
)
),"ID",[Order ID],"invoice dates",[Date])
