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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kanagaraj2201PB
New Member

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

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.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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

52.png

 

 

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.

View solution in original post

1 REPLY 1
v-easonf-msft
Community Support
Community Support

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

52.png

 

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.