Balance end of month

[Tormod_GK]

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

[PavelR]

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

[Tormod_GK]

Hi.

I see that I was a bit indistinctly.

I have table 1, but not table 2. So I would like to create table 2 based on my table 1.

[v-ljerr-msft]

@Tormod_GK

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