DATESINPERIOD multiple values supplied?

[Anonymous]

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) 

[v-lili6-msft]

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

[Anonymous]

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

[Anonymous]

@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])

[v-lili6-msft]

hi,@dataman123

     After my research, You can do these follow my steps like below:

Step1:

Improve your formula :

Table 2 = 
SELECTCOLUMNS(
GENERATE(
'Table11',
FILTER(
CALENDAR(MIN('Table11'[Added Date]),MAX(Table11[End Date]))
,[Date]>=[Added Date] && [Date] <= IF(NOT IsBlank([End Date]),[End Date],MAX('Table11'[End Date])
)
)
),"ID",[OrderID],"invoice dates",[Date],"Add Date",[Added Date])

Step2:

Add a column:

sameday permonth = IF(DATEADD('Table 2'[invoice dates],DATEDIFF('Table 2'[invoice dates],'Table 2'[Add Date],MONTH),MONTH)='Table 2'[Add Date],"same day permonth")

Step3:

filter the data by this column

You can filter data in many ways, for example: drag it into page level filters or report level filters, etc.

Result:

Here is demo, please try it.

https://www.dropbox.com/s/xtynyqkzffzskc6/DATESINPERIOD%20multiple%20values%20supplied.pbix?dl=0

Best Regards,

Lin

[v-lili6-msft]

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

[Anonymous]

thats perfect but it seems on the date column yours is looking at days i need to look at months? is there a way i can do this?

[Anonymous]

Please see an example of the data? how would this be used with your formula? the added date would be the start date.

[v-lili6-msft]

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