Calculate Every Month End Closing Balance/Amount

[rpavan17]

Hi,

I have two Tables

Table 1:

Vendor,

Date_Posting,

Date_Clearing,

Amount

Table2:

Month_End_Date;

I have to get the sum of Amount where Date_Posting < Month_End AND Date_Clearing > Month_End AND 

SUM(AMOUNT)>0 FOR EACH VENDOR .

Can you please help me here?

If i filter with vendor, it should show only selected vendor amount

Many Thanks

[Anil_Babu_Samineni]

Try this

Sum({<YourPrimaryKey={"=Date_Cleaning>Month_End and Date_Posting<Month_End"}>} AMOUNT)

[Taoufiq_Zarra]

Hi,

in the load script?

can you share sample data ?

[rpavan17]

Data:
Load *Inline[
Company, Vendor, Date_Posting, Date_Clearing, Amount
5000, 147, 02-02-2019, 06-06-2019, -245
4000, 157, 03-03-2019, 07-07-2019, -234
5000, 157, 03-04-2018, 11-09-2019, -244
5000, 154, 02-03-2019, 06-07-2019, 245
5000, 147, 02-02-2018, 06-08-2018, -322
5000, 134, 02-02-2018, 06-08-2018, -312
4000, 157, 01-10-2019, 03-11-2019, 554
];

Month_End:

Load Distinct
Date(Monthend(Month),'DD-MM-YYYY') as Month_End

Resident Master_Calendar;

Master_Calendar contains dates from last few years.

Expecting Dimension as Month_End, to see the amount which are greater than 0 in measure

[rpavan17]

It gives null value

Sum({<VendorCompany={"=Date_Cleaning>Month_End and Date_Posting<Month_End"}>} AMOUNT)

[rpavan17]

Hi,

Expecting Dimension as Month_End, to see the amount which are greater than 0 

[Kushal_Chawda]

Does Table 2 Contain Vendor? Id the Month_end_date is static value?

[rpavan17]

Month_Date is static value and does not contain vendor in this table.

This is just a date.

[Kushal_Chawda]

I have two Tables

Table2:

Month_End_Date

FROM Table2;

let vMonthEnd = peek('Month_End_Date',0,'Table2');

Table 1:

Vendor,

Date_Posting,

Date_Clearing,

if(Date_Posting < $(vMonthEnd) and Date_Clearing > $(vMonthEnd),1,0) as Flag

Amount

FROM Table1;

Now you can use the expression as below

=Sum({<Flag={1}>}Amount)

[rpavan17]

I would like to see only sum greater than 0 for everymonth end date in calendar