cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tormod_GK
Frequent Visitor

Balance end of month

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  
ArticleNoStockBalanceDate
7WT01000962015-07-28 11:08:44.000
7WT010009112015-07-28 11:20:02.000
7WT010009102015-07-29 08:43:05.000
7WT01000952015-07-29 08:47:24.000
7WT01000942016-03-14 15:07:04.000
7WT010009142016-08-23 15:39:06.000
   
Table #2  
ArticleNoStockBalanceDate
7WT01000952015-07-31 00:00:00.000
7WT01000952015-08-31 00:00:00.000
7WT01000952015-09-30 00:00:00.000
7WT01000952015-10-31 00:00:00.000
7WT01000952015-11-30 00:00:00.000
7WT01000952015-12-31 00:00:00.000
7WT01000952016-01-31 00:00:00.000
7WT01000952016-02-28 00:00:00.000
7WT01000942016-03-31 00:00:00.000
7WT01000942016-04-30 00:00:00.000
7WT01000942016-05-31 00:00:00.000
7WT01000942016-06-30 00:00:00.000
7WT01000942016-07-31 00:00:00.000
7WT010009142016-08-31 00:00:00.000

 

 

3 REPLIES 3
PavelR
Solution Specialist
Solution Specialist

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
Frequent Visitor

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.

@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.

t1.PNG

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] )

date.PNG

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 ()
    )

endofmonth.PNG

3. Create Table 2 from table 1 by filtering column "Is End of Month".

Table 2 = CALCULATETABLE ( Table1, Table1[Is End of Month] = TRUE () )

t2.PNG

Regards

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!