Inventory Last Year

Hi YZORI ,

My solution don't need the calendar table.

Create a Year column and a Month column in your main table.

Create a measure to return the latest date in the same month of last year.

The latest date in the same month of last year =
CALCULATE (
    MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
    FILTER (
        ALLSELECTED ( PowerQuery_Inventory ),
        [Year]
            = MAX ( 'PowerQuery_Inventory'[Year] ) - 1
            && [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
    )
)

You can create another metric or, like me, modify the metric to get the sum of the values of the latest date of the same month last year.

Inventory Last Year =
VAR latestdate =
    CALCULATE (
        MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
        FILTER (
            ALLSELECTED ( PowerQuery_Inventory ),
            [Year]
                = MAX ( 'PowerQuery_Inventory'[Year] ) - 1
                && [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
        )
    )
RETURN
    CALCULATE (
        SUM ( PowerQuery_Inventory[RetailAmt] ),
        FILTER ( ALLSELECTED ( PowerQuery_Inventory ), [FileCreationDate] = latestdate )
    )

Best Regards,

Stephen Tao

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