Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi.
This is probably very difficult. For each article I would like a balance for the end of the month. So from table #1 I would like to receive the data in table #2. Is this possible?
Table #1 | ||
ArticleNo | StockBalance | Date |
7WT010009 | 6 | 2015-07-28 11:08:44.000 |
7WT010009 | 11 | 2015-07-28 11:20:02.000 |
7WT010009 | 10 | 2015-07-29 08:43:05.000 |
7WT010009 | 5 | 2015-07-29 08:47:24.000 |
7WT010009 | 4 | 2016-03-14 15:07:04.000 |
7WT010009 | 14 | 2016-08-23 15:39:06.000 |
Table #2 | ||
ArticleNo | StockBalance | Date |
7WT010009 | 5 | 2015-07-31 00:00:00.000 |
7WT010009 | 5 | 2015-08-31 00:00:00.000 |
7WT010009 | 5 | 2015-09-30 00:00:00.000 |
7WT010009 | 5 | 2015-10-31 00:00:00.000 |
7WT010009 | 5 | 2015-11-30 00:00:00.000 |
7WT010009 | 5 | 2015-12-31 00:00:00.000 |
7WT010009 | 5 | 2016-01-31 00:00:00.000 |
7WT010009 | 5 | 2016-02-28 00:00:00.000 |
7WT010009 | 4 | 2016-03-31 00:00:00.000 |
7WT010009 | 4 | 2016-04-30 00:00:00.000 |
7WT010009 | 4 | 2016-05-31 00:00:00.000 |
7WT010009 | 4 | 2016-06-30 00:00:00.000 |
7WT010009 | 4 | 2016-07-31 00:00:00.000 |
7WT010009 | 14 | 2016-08-31 00:00:00.000 |
Hi @Tormod_GK. How exactly should the result look? What you mean by "from table #1 I would like to receive the data in table #2"?
Regards.
Pavel
In this scenario, you can add a custom column called "Is End of Month" in Table 1 to check if the Date is the end of that month first(using ENDOFMONTH function), then create Table 2 from table 1 by filtering this column. See my sample below.
I assume you have table called Table 1 like below.
1. In order to use ENDOFMONTH function, a standard Date table is needed. If you don't have one, you can use CALENDAR or CALENDARAUTO function to create one, and use column Date to create a relationship between Table 1 and the Date table. And use the formula below to add a calculate column called "YearMonth" in the Date table.
YearMonth = YEAR ( DateTable[Date] ) & MONTH ( DateTable[Date] )
2. Use the formula below to add a custom column called "Is End of Month" in Table 1 to check if the Date is the end of that month.
Is End of Month = VAR ym = RELATED ( DateTable[YearMonth] ) RETURN IF ( CALCULATE ( ENDOFMONTH ( DateTable[Date] ), ALL ( DateTable ), DateTable[YearMonth] = ym ) = Table1[Date], TRUE () )
3. Create Table 2 from table 1 by filtering column "Is End of Month".
Table 2 = CALCULATETABLE ( Table1, Table1[Is End of Month] = TRUE () )
Regards