Forum Discussion
Filtering through Power BI Measures
I have a date calculated through a measure. I want to use this date to return the value of another measure:
CALCULATED DATE
Date Variation = MAX('Calendar'[Date]) - [PMP]
TOTAL STOCK
Total Stock =
SUMX(FILTER(
Stock,
(Stock[AccountName] = "account1" ||
Stock[AccountName] = "account2" ||
Stock[AccountName] = "account3"
),
Stock[Value]
MEASURE FILTERED BY ANOTHER MEASURE
Total Stock PMP =
CALCULATE(
[Total Stock],
FILTER('Calendar',
'Calendar'[Date] = [Date Variation]
))
However, the output of Total Stock PMP returns BLANK because of [Date Variation] = 18/11/2022. This happens due to the specific day set by the filter. There are no values in the stock on the 18th.
I want to find a method that does not filter exactly this date but instead only the month and year, thus taking the entire month's range!
I've tried using ALL, STARTOFMONTH, ENDOFMONTH, FORMAT. However, all of them give some kind of error.
I managed to achieve the final result through this:
Total Stock PMP =
VAR month = MONTH([Date Variation])
VAR year = YEAR([Date Variation])RETURN
CALCULATE(
[Total Stock],
FILTER (
ALL('Calendar'),
'Calendar'[MonthN] = month &&
'Calendar'[Year] = year
)
)Thank you for your attention.
Best regards!
2 Replies
I managed to achieve the final result through this:
Total Stock PMP =
VAR month = MONTH([Date Variation])
VAR year = YEAR([Date Variation])RETURN
CALCULATE(
[Total Stock],
FILTER (
ALL('Calendar'),
'Calendar'[MonthN] = month &&
'Calendar'[Year] = year
)
)Thank you for your attention.
Best regards!
