Calculate the Inventory $ of the Last Week in Previous Month

[felixzhao123]

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

[felixzhao123]

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

[saurabhtd]

@felixzhao123 You are welcome.

[saurabhtd · Accepted solution]

@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
    )