Forum Discussion

YZORI's avatar
YZORI
New Member
3 years ago
Solved

Inventory Last Year

Hi all,   I need some help in order to calculate the inventory of any given period for previous year. Currently I am using the current DAX measure to calculate the latest inventory of any given mon...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.           

     

bhelou's avatar
bhelou
Responsive Resident
3 years ago
INV PY ($) = 
CALCULATE(
    SUM(PowerQuery_inventory[RetailAmt]),
    FILTER(
        ALL(PowerQuery_inventory),
        YEAR(PowerQuery_inventory[FileCreationDate]) = YEAR(TODAY())-1 &&
        MONTH(PowerQuery_inventory[FileCreationDate]) = MONTH(DATEADD(DATESYTD(TODAY()), -12, MONTH)) &&
        PowerQuery_inventory[FileCreationDate] = CALCULATE(
            MAX(PowerQuery_inventory[FileCreationDate]),
            ALL(PowerQuery_inventory),
            YEAR(PowerQuery_inventory[FileCreationDate]) = YEAR(TODAY())-1,
            MONTH(PowerQuery_inventory[FileCreationDate]) = MONTH(DATEADD(DATESYTD(TODAY()), -12, MONTH))
        )
    )
)


//////You can copy and paste this code into your DAX editor and replace "PowerQuery_inventory" with the name of your inventory table.
YZORI's avatar
YZORI
New Member
3 years ago

Hey bhelou unfortanutely this didn't work for me. I've added a sample of the data here for your reference: sample_data.pbix