Vendor Statement of Account

[nadir]

Hi
I am trying to create a Vendor Statement of Accout based on Slicer date selection  from a data that has Debit,Credit , Date among other fields and a Calendar table , the required output is
Opening Balance = Sum of Net Change amounts before minimum date selected as measure below

               Total Opening Balance = CALCULATE([Total Debit Minus Credit],

                filter(ALL('Calendar'),

                'Calendar'[Date] < MIN('Calendar'[Date])

                  )

                 )

Debit = Sum of Debit Amounts
Credit = Sum of Credit Amounts
Net Change = Debit - Credit
Ending Balance = Opening Balance + Net Change
Issue:
Summary report for a date range 01/01/2019 till 31/12/2019is working fine as below

Vendor ID	Name	Opening Balance	Debit	Credit	net chamge	Ending balance
20177	Name 1	0	48059	48059	0	0

but detasiled report is showing all transactions before minimum  selected date for the Opening Balance , in other words the data before minimum selected date should be summed up in one total amount and show as opening balance as one record in the report

Vendor ID	Name	Doc Number	Date	Description	Opening Balance	Debit	Credit	net chamge	Ending balance
20177	Name 1	INV000000040953	19/06/2014	Description 1	(53,000.00)				(53,000.00)
20177	Name 1	2037	19/06/2014	Description 1	17,000.00				17,000.00
20177	Name 1	2127	28/09/2014	Description 1	12,000.00				12,000.00
20177	Name 1	2220	15/12/2014	Description 1	12,000.00				12,000.00
20177	Name 1	2355	26/04/2015	Description 1	12,000.00				12,000.00
20177	Name 1	12-129	23/12/2015	Description 1	(12,000.00)				(12,000.00)
20177	Name 1	2368	23/12/2015	Description 1	12,000.00				12,000.00
20177	Name 1	2744	22/03/2016	Description 1		12,000.00	12,000.00	-	-
20177	Name 1	2484	15/06/2016	Description 1		12,000.00	-	12,000.00	12,000.00
20177	Name 1	Jun-16	15/06/2016	Description 1		-	12,000.00	(12,000.00)	(12,000.00)
20177	Name 1	09-Oct	19/09/2016	Description 1		-	12,000.00	(12,000.00)	(12,000.00)
20177	Name 1	2936	28/09/2016	Description 1		12,000.00	-	12,000.00	12,000.00
20177	Name 1	Dec-79	31/12/2016	Description 1		-	12,059.00	(12,059.00)	(12,059.00)
20177	Name 1	3012	31/12/2016	Description 1		12,059.00	-	12,059.00	12,059.00

Any help will be appreciated
Nadir

[v-yingjl]

Hi, @nadir  ，

According to your description, if you want to create a dynamic calculated table based on the slicer, I am afraid that it is not supported in power bi  becasue not like measures, the calculated column/table is calculated during database processing (such as data refresh) and then stored in the model , They will not respond to user choices on the report.

One way to create a new table that meets your requirements is that you can create a calculated table like this and need to manually change the date in the filter formula as needed.

Table =
VAR tab1 =
    FILTER (
        ALL ( 'Vendor Statement' ),
        'Vendor Statement'[Date] >= DATE ( 2016, 1, 1 )
            && 'Vendor Statement'[Date] <= DATE ( 2016, 12, 31 )
    )
VAR tab2 =
    FILTER (
        ALL ( 'Vendor Statement' ),
        NOT (
            'Vendor Statement'[Date] >= DATE ( 2016, 1, 1 )
                && 'Vendor Statement'[Date] <= DATE ( 2016, 12, 31 )
        )
    )
VAR _row =
    ROW (
        "Vendor ID", MAXX ( tab2, [Vendor ID] ),
        "Name", MAXX ( tab2, [Name] ),
        "Doc Number", "Summarize doc",
        "Date", MAXX ( tab2, [Date] ),
        "Description", MAXX ( tab2, [Description] ),
        "Opening Balance", SUMX ( tab2, [ Opening Balance] ),
        "Debit", SUMX ( tab2, [Debit] ),
        "Credit", SUMX ( tab2, [Credit] ),
        "net change", SUMX ( tab2, [net chamge] ),
        "Ending balance", SUMX ( tab2, [Ending balance] )
    )
RETURN
    UNION ( tab1, _row )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nadir]

Thanks 

Really appreciate your response and help but this will be a sort of static data and report where the consumer of the report can not select a different date than what is already specified in the new created table , the issue here is that this statement should be printed for a different date selection everytime.

Regards

Nadir

[v-yingjl]

Hi @nadir ,

As I previous posted, power bi could not support create dynamic calculated table based on selected slicer.

Please refer:

1. Dynamic Table based on Slicers 
2. Based-on-slicer-selection-create-dynamic-calculated-table-in-power-bi 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @nadir 

Can you share the pbix?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[nadir]

Hi AIB,

Sorry for larte reply.

I am sending the link for an excel file that has all the transactions which will be used along with a normal date table , I can not shatre the PBIX file since it has company data.

Another point the report date is 01/01/2016 till 31/12/2016

https://www.dropbox.com/scl/fi/s1xnho0vxawrybyebpdwp/Vendor-Statement.xlsx?dl=0&rlkey=aj6gcjeiv35q5q...

Rgeards

Nadir