Projected Total Expired Items by Year

[Learner_44]

Hi All,

I have a table of 'Items' which has a column for the Item Line each instance is part of. These item lines have expiration dates as captured in the 'Item Lines' table. What I'm trying to generate is a 100% stacked column chart for number of expired items currently and projected into future years.

For the following tables:

'Items'

Item ID	Item Line	Location
1	Line A	Building X
2	Line B	Building X
3	Line B	Building X
4	Line C	Building X
5	Line A	Building Y
6	Line A	Building Y
7	Line C	Building Y
8	Line C	Building Y
9	Line C	Building Z
10	Line C	Building Z

'Item Lines'

Item Line	Expiration Date
Line A	5/1/2023
Line B	8/7/2024
Line C	1/3/2025

I want an output that looks like the following:

This chart was generated using the manually calculated Excel data below:

Date	Num Expired	Num Not Expired	Notes
1/1/2023	0	10
1/1/2024	3	7	Line A has expired
1/1/2025	5	5	Lines A and B have expired
1/1/2026	10	0	All Lines are past expiration

Been trying to make this a single measure and then context will take care of the difference between years but can't figure it out. Thanks in advance!

[v-rzhou-msft · Accepted solution]

Hi @Learner_44 ,

Here I create a sample to have a test.

Firstly, I suggest you to create a calculated table to show years and first day of each year.

Year and Date =
ADDCOLUMNS ( GENERATESERIES ( 2023, 2026 ), "Date", DATE ( [Value], 01, 01 ) )

Measures:

Num Expired =
VAR _EXPIRELIST =
    CALCULATETABLE (
        VALUES ( 'Item Lines'[Item Line] ),
        FILTER (
            'Item Lines',
            'Item Lines'[Expiration Date] <= MAX ( 'Year and Date'[Date] )
        )
    )
RETURN
    COUNTAX ( FILTER ( 'Items', Items[Item Line] IN _EXPIRELIST ), Items[Item ID] ) + 0

Num Not Expired =
VAR _EXPIRELIST =
    CALCULATETABLE (
        VALUES ( 'Item Lines'[Item Line] ),
        FILTER (
            'Item Lines',
            'Item Lines'[Expiration Date] > MAX ( 'Year and Date'[Date] )
        )
    )
RETURN
    COUNTAX ( FILTER ( 'Items', Items[Item Line] IN _EXPIRELIST ), Items[Item ID] ) + 0

Note =
VAR _EXPIRELIST =
    CALCULATETABLE (
        VALUES ( 'Item Lines'[Item Line] ),
        FILTER (
            'Item Lines',
            'Item Lines'[Expiration Date] <= MAX ( 'Year and Date'[Date] )
        )
    )
VAR _COUNT =
    COUNTX ( _EXPIRELIST, [Item Line] )
VAR _ALLLIST =
    COUNTX ( ALL ( 'Item Lines'[Item Line] ), [Item Line] )
RETURN
    IF (
        _COUNT = _ALLLIST,
        "All Lines are past expiration",
        IF (
            _COUNT = 0,
            BLANK (),
            CONCATENATEX ( _EXPIRELIST, [Item Line], " , " ) & " " & "has expired"
        )
    )

Num Expired % = 
VAR _TOTAL = CALCULATE(COUNT(Items[Item ID]),ALL(Items))
RETURN
DIVIDE([Num Expired],_TOTAL)

Num Not Expired % = 
VAR _TOTAL = CALCULATE(COUNT(Items[Item ID]),ALL(Items))
RETURN
DIVIDE([Num Not Expired],_TOTAL)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.