Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |