Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
)
@saurabhtd your method works perfect! Thanks and have already accepted as solution. Thanks!
@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
)
User | Count |
---|---|
57 | |
21 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |