Forum Discussion
Measures Creating Error
Hi Rich_Wyeth ,
To ensure that the two measures (M_CurrentQuotes and M_CurrentInvoices) work correctly together in the same visual, follow these best practices:Use FILTER() to Apply Row-Based Logic
FILTER() enables row-wise evaluation of conditions (e.g., checking if a field is a quote or an invoice).
This ensures a valid row context for the CALCULATE() function to operate correctly.
Using Boolean functions like ISTEXT() or ISBLANK() directly in CALCULATE() without FILTER() can create ambiguous or invalid filter contexts, especially when used in visuals with multiple measures.Isolate Time Intelligence Logic:
Use variables to define the required time period (e.g.,VAR LastMonth = MONTH(TODAY()) - 1), and apply the logic through a separate FILTER() on the Date table.For robust and dynamic time calculations, prefer built-in DAX time intelligence functions such as:
DATESINPERIOD(), DATEADD(), SAMEPERIODLASTYEAR()
These functions handle date transitions (e.g., across year boundaries) and maintain compatibility with visuals and slicers that rely on date hierarchies.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you
Rich_Wyeth Try updating measure as
M_CurrentQuotes =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISTEXT('Margin Reports'[CQ Number]),
'Dates'[MonthNumber] = MONTH(TODAY()) - 1,
'Dates'[Year] = YEAR(TODAY())
)
DAX
M_CurrentInvoices =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISBLANK('Margin Reports'[CQ Number]),
'Dates'[MonthNumber] = MONTH(TODAY()) - 1,
'Dates'[Year] = YEAR(TODAY())
)
DAX
M_YTDQuotes =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISTEXT('Margin Reports'[CQ Number]),
DATESYTD('Dates'[Date])
)
DAX
M_YTDInvoices =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISBLANK('Margin Reports'[CQ Number]),
DATESYTD('Dates'[Date])
)
DAX
M_PreviousYTDQuotes =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISTEXT('Margin Reports'[CQ Number]),
SAMEPERIODLASTYEAR(DATESYTD('Dates'[Date]))
)
DAX
M_PreviousYTDInvoices =
CALCULATE(
SUM('Margin Reports'[T PRICE]),
ISBLANK('Margin Reports'[CQ Number]),
SAMEPERIODLASTYEAR(DATESYTD('Dates'[Date]))
)
Hi,
Thank you, unfortunately I still get an Error. The data I hold is 2024 and 2025 to date. The data is all in one table, but there is no guarantee that a record for a Quote also has an Invoice. I did create another measure that had an IF, to say if there was a value to calculate , then use the calculation, else 0, as I thought perhaps the mismatch of companies might be an issue. But when I add the second measure it errors the table, still. If I put either measure in the table on its own it works, but once I try to put in the second, it just fails.
Hi Rich_Wyeth ,
To ensure that the two measures (M_CurrentQuotes and M_CurrentInvoices) work correctly together in the same visual, follow these best practices:Use FILTER() to Apply Row-Based Logic
FILTER() enables row-wise evaluation of conditions (e.g., checking if a field is a quote or an invoice).
This ensures a valid row context for the CALCULATE() function to operate correctly.
Using Boolean functions like ISTEXT() or ISBLANK() directly in CALCULATE() without FILTER() can create ambiguous or invalid filter contexts, especially when used in visuals with multiple measures.Isolate Time Intelligence Logic:
Use variables to define the required time period (e.g.,VAR LastMonth = MONTH(TODAY()) - 1), and apply the logic through a separate FILTER() on the Date table.For robust and dynamic time calculations, prefer built-in DAX time intelligence functions such as:
DATESINPERIOD(), DATEADD(), SAMEPERIODLASTYEAR()
These functions handle date transitions (e.g., across year boundaries) and maintain compatibility with visuals and slicers that rely on date hierarchies.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you
