cancel
Showing results for
Did you mean:
New Member

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],
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.
1 ACCEPTED SOLUTION
Community Support

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 5
Community Support

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.

New Member

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.

Responsive Resident
``````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.
``````
New Member

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

Responsive Resident

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 ,

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors