topic Re: Calculate the Inventory $ of the Last Week in Previous Month in DAX Commands and Tips

Calculate the Inventory $ of the Last Week in Previous Month

felixzhao123 — Mon, 15 Jan 2024 03:57:46 GMT

Hi,

I have a dataset which includes weekly inventory data of my company. I would like to compare latest inventory $ against the end of last month. However, normally there are four weeks data in previous month, and i would like to use the last week's data in previous month as the ending inventory $. A short example would be as below:

The latest inventory $ is Jan 10 2024 and i would like to get the inventory of Dec 27 2023 inventory $ even though there is another week (Dec 20).

How can i achieve this with DAX?

Thanks,

Felix

Re: Calculate the Inventory $ of the Last Week in Previous Month

saurabhtd — Mon, 15 Jan 2024 04:11:07 GMT

felixzhao123 Here is the DAX logic which might work in your case.

Inventory Last Week in Previous Month = VAR CurrentDate = TODAY() VAR LastWeekPreviousMonth = CALCULATE( MAX('Table'[Data Cut-off Date]), FILTER( ALL('Table'), MONTH('Table'[Data Cut-off Date]) = MONTH(EOMONTH(CurrentDate, -1)) && YEAR('Table'[Data Cut-off Date]) = YEAR(EOMONTH(CurrentDate, -1)) ) ) RETURN CALCULATE( SUM('Table'[Sum of Inv USS]), 'Table'[Data Cut-off Date] = LastWeekPreviousMonth )

Re: Calculate the Inventory $ of the Last Week in Previous Month

felixzhao123 — Mon, 15 Jan 2024 04:26:50 GMT

saurabhtd your method works perfect! Thanks and have already accepted as solution. Thanks!

Re: Calculate the Inventory $ of the Last Week in Previous Month

saurabhtd — Mon, 15 Jan 2024 04:30:02 GMT

felixzhao123 You are welcome.