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 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.
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.           

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.           

     

    • YZORI's avatar
      YZORI
      New Member
      3 years ago

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

  • bhelou's avatar
    bhelou
    Responsive Resident
    3 years ago

    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 , 

  • 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.