Forum Discussion
Helper IISummarising monthly data based on Cut-off dates table
Hi,
I have 2 tables:
1. Dataset with clockings with date, employee number, number of hours worked, pay etc.
2. Cutoff dates with Reference date, Cut-off start date and Cut-off end date. It's manually filled.
I need to make it in a way so it will show values for months considering cut-off to cutoff dates. For example, June 2022 will include dates from 25.05.2022 to 23.06.2022 and show the labour cost only for the period between those dates.
I wrote a metric with fixed cutoff dates but those dates are changing from month to month so I came up with the idea to connect the mentioned datasets but can't figure out how.
May I ask for your assistance?
Danielnir , You can populate the month name as new column in table1 and then you can use that in join
new column in table1
maxx(filter(Table2, Table2[Strat Date]<=Table1[Date] && Table2[EndDate]<=Table1[Date] ), Table2[Month])
3 Replies
- amitchandak
Super User
Danielnir , You can populate the month name as new column in table1 and then you can use that in join
new column in table1
maxx(filter(Table2, Table2[Strat Date]<=Table1[Date] && Table2[EndDate]<=Table1[Date] ), Table2[Month])
- Danielnir
Thanks, that did the trick.
