Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 , 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
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.
@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))