Create table with distinct count and sum of columns by month if its between 2 month dates

[naejf]

Hello,

I have a Table1 like this:

day	id	account	amount	day + 60 days
14/09/2020	1	1	10	14/11/2020
15/09/2020	2	1	20	15/11/2020
17/09/2020	4	0	0	17/11/2020
22/09/2020	4	1	10	22/11/2020
23/09/2020	4	1	10	23/11/2020
2/10/2020	2	1	30	2/12/2020
3/10/2020	3	1	20	3/12/2020
7/10/2020	5	1	10	7/12/2020
12/11/2020	5	1	10	12/01/2021
13/11/2020	1	1	20	13/01/2021
19/11/2020	2	1	30	19/01/2021
24/11/2020	3	1	20	24/01/2021
26/11/2020	6	0	20	26/01/2021
15/12/2020	6	1	20	14/02/2021

The last column is only the first one + 60 days interval.

This table has a relationship with a Time Table created with autocalendar.

I would like create a Table 2 like this:

monthyear	distinct count id	sum amount
1/09/2020	3	50
1/10/2020	5	110
1/11/2020	6	210
1/12/1920	5	180
1/01/2021	5	120
1/02/2021	1	20

Where I'd have a column with all monthyears in both 'day' column and 'day + 60 days' column of Table1; and for each monthyear , the disctinct count of Ids where monthyear of Table2 is between 'day' and 'day + 60 days', and the sum of amount where again monthyear of Table2 is between 'day' and 'day + 60 days'.

Thanks a lot for your help.

[amitchandak · Accepted solution]

@naejf , Try to create second table like

Table 2= Addcolumns(generateseries(1,15, 1), "Date",Date(2020, 8+[Value],1))

I doubt 1 sep between these dates

If required use end date

end Date = eomonth([Date], 0) 

And a new column = calculate(Distinctcount(Table1[ID]), filter(Table1[Day]<= Table2[Date] && Table1[Date] >= Table2[Date]))

Amount = calculate(sum(Table1[amount]), filter(Table1[Day]<= Table2[Date] && Table1[Date] >= Table2[Date]))

I doubt range logic is correct as per table given  above

[naejf]

Hello @amitchandak,

Thanks for your help, your advice guided me to the logic of what I expected.

Only one question, Is there a way that Date column you created in Table2 could be created based in columns 'date' and 'date+60' of Table1 (like for example, min('date') and max('date'), or min('date') and max('date+60') )? I wonder this because the data I put is a sample of my real data, which update daily and months will increse, so I wouldn't like to have to change it manually.

Please, if you could give some advice with this last request, I'd really appreaciate.

[amitchandak]

@naejf , Try like

Table 2=

Var _diff = datediff(min(Table1[Day]), Max(Table1[End Date]),Month) // add +1 if needed

Var _st = min(Table1[Day])

return

Addcolumns(generateseries(1,_diff , 1), "Date",Date(year(_st), Month(_st)+[Value],1))

[naejf]

@amitchandak It did work! Thanks so much.