How to show quantity drop off for expiries

[stevenls]

I feel like I am missing something really obvious here.

I have two tables, one is a date table and one is some maintenance contract expiration data from our purchasing system.

I am simply trying to create a visualisation which will show a horizontal line with the total items and then where that drops off when there is an expiry, so basically the report should drop down over time.

Supplier	Maint End Date	Qty
A	13/03/2023	5
A	19/06/2023	10
A	10/10/2023	5

Basically I just want to show a line chart that up until March 2023 the total would be 20, then from April 2023, the total is 15, then from July the total is 5.

There is a relationship between Date in the Date table and Maint End Date in the Expiries table.

I have tried a measure that was the sum of the quantity, filtered agains the end date where it was >= the date in the date table but I am just getting a horizontal line that doesn't drop off.

As I said I think this should be simple but I am just not getting it.

[amitchandak · Accepted solution]

@stevenls , You can create a date table and do not create a join then you can try a measure like

measure =

calculate(Sum(Table[Qty]) , Filter(Table, Table[Main End Date] <= MAx('Date'[Date]))

or

measure =

35- calculate(Sum(Table[Qty]) , Filter(Table, Table[Main End Date] <= MAx('Date'[Date]))

Date from date table should be in visual, if you need trend

[stevenls]

Thanks for that, I am underway with this now.