Forum Discussion

Learner_44's avatar
Learner_44
New Member
3 years ago
Solved

Projected Total Expired Items by Year

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 IDItem LineLocation
1Line ABuilding X
2Line BBuilding X
3Line BBuilding X
4Line CBuilding X
5Line ABuilding Y
6Line ABuilding Y
7Line CBuilding Y
8Line CBuilding Y
9Line CBuilding Z
10Line CBuilding Z

 

'Item Lines'

Item LineExpiration Date
Line A5/1/2023
Line B8/7/2024
Line C1/3/2025

 

I want an output that looks like the following:

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

DateNum ExpiredNum Not ExpiredNotes
1/1/2023010 
1/1/202437Line A has expired
1/1/202555Lines A and B have expired
1/1/2026100All 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!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.