Inventory Last Year

[YZORI]

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 month (sample data: sample_data.pbix😞

    CY INV = CALCULATE( SUM ( PowerQuery_inventory[RetailAmt]), LASTDATE(PowerQuery_inventory[FileCreationDate]))

Initially I used this measure to calculate the LY inventory's value:

INV PY ($) =

CALCULATE(SUM(PowerQuery_inventory[RetailAmt]),

        DATESBETWEEN(Dates_table[Dates],                    

            FIRSTDATE(DATEADD(Dates_table[Dates],-12,MONTH)),  

            IF(MONTH(MIN(Dates_table[Dates])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD(Dates_table[Dates],-12,MONTH)))

         )

     )

However this doesn't work as it adds up the inventory value for each week's entry.... I only need to return the inventory value for whatever the latest available date is for that particular month (does not necessarily mean the last date of the month). See theoritical example below, in this case it should only return the value 300 and not the total of 550 (which is currently the case).

Any help is appreciated.

[v-stephen-msft · Accepted solution]

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.           

[YZORI]

Thank you @v-stephen-msft - I have modified it slightly to make it work for my working file - but this put me in the right direction. 

[bhelou]

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]

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

[bhelou]

Hello , please can you share some sample of the PBIX file ? , its a bit hard to guess what is happening here for the last year calculation . 
you can share a link via dropbox or drive . 


Regards ,