Aging Report : Want to filter and return field value based on date range

[Kanagaraj2201PB]

Hi to all,

Thanks in advance!!!

I am beginner to Power BI Dashboard development and got one new requirement in power BI to generate Matrix based on AGING values.

I have two tables:

Table – 1: Definition Table

Name	Aging	Qty	Line Num
DEF 01	Age 0	0	1
DEF 01	Age 1	30	-1
DEF 01	Age 2	30	-2
DEF 01	Age 3	30	-3
DEF 01	Age 4	1	-4

Table 2: Vendor Transactions

Vendor	Name	Invoice Date	Invoice Id	Amount
V-001	V-001	31-05-2021	INV-001	1000
V-001	V-001	15-06-2021	INV-002	2000
V-002	V-002	02-06-2021	INV-003	1500
V-002	V-002	15-07-2021	INV-004	3000
V-003	V-003	16-06-2021	INV-005	5000
V-004	V-004	19-08-2021	INV-006	2500
V-004	V-004	01-06-2021	INV-007	3500
V-004	V-004	31-05-2021	INV-008	4000

 Scenario:

1. I need to modify Table-1 Like below based on selected date from date Dimension Slicer.

Selected date from Date slicer is	31-05-2021
Name	Aging	Qty	Line Num	Aging Qty	Index	Start date : ((selected date + AgingQty)-Qty) --> If Index 1 and Qty = 0 ((selected date + AgingQty)-Qty) + 1 àFor all MaxDate()  of date table  --> If Index = max() index field)	End date: (Selected date + Aging Qty)
DEF 01	Age 0	0	1	0	1	31-05-2021	31-05-2021
DEF 01	Age 1	30	-1	30	2	01-06-2021	30-06-2021
DEF 01	Age 2	30	-2	60	3	01-07-2021	30-07-2021
DEF 01	Age 3	30	-3	90	4	31-07-2021	29-08-2021
DEF 01	Age 4	1	-4	91	5	30-08-2021	30-08-2050

I could be modified table - 1 like above with the help Calculated Measures in Power BI.

My real need is to Create “Aging value” field in vendor Transaction table.

Need to Get corresponding ‘Aging” field value from Table-1 and condition is current selected Invoice Date value should be in date range of Start and End Date field from Table-1.

Vendor	Name	Invoice Date	Invoice Id	Amount	Aging Value
V-001	V-001	31-05-2021	INV-001	1000	Age 0
V-001	V-001	15-06-2021	INV-002	2000	Age 1
V-002	V-002	02-06-2021	INV-003	1500	Age 1
V-002	V-002	15-07-2021	INV-004	3000	Age 2
V-003	V-003	16-06-2021	INV-005	5000	Age 1
V-004	V-004	19-08-2021	INV-006	2500	Age 3
V-004	V-004	01-06-2021	INV-007	3500	Age 1
V-004	V-004	31-05-2022	INV-008	4000	Age 4

Please guide me to achieve this.

[v-easonf-msft · Accepted solution]

Hi, @Kanagaraj2201PB 

If you have modifed table1,pelase try to create a calculated column as below:

Aging value =
VAR date1 = 'Vendor Transactions'[Invoice Date]
RETURN
    CALCULATE (
        MAX ( 'Modified Table'[Aging] ),
        FILTER (
            'Modified Table',
            date1 >= 'Modified Table'[Start date]
                && date1 <= 'Modified Table'[End date]
        )
    )

If it desn't meet your requirement, please share a sample pbix file for further research.

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