Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.