cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to show quantity drop off for expiries

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.

1 ACCEPTED SOLUTION
Super User

@Anonymous , 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

2 REPLIES 2
Super User

@Anonymous , 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

Anonymous
Not applicable

Thanks for that, I am underway with this now.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors