Measures Creating Error

[Rich_Wyeth]

Hi all,

I have a dataset that holds details on Invoices, and Quotes. I have taken the company name into a table and then I have added the following measure for Quotes: 

M_CurrentQuotes = CALCULATE(SUM('Margin Reports'[T PRICE]),ISTEXT('Margin Reports'[CQ Number]),'Dates'[MonthNumber]=MONTH(NOW())-1)

I then want a second column for Invoices, so the Measure is this:

M_CurrentInvoices = CALCULATE(SUM('Margin Reports'[T PRICE]),ISBLANK('Margin Reports'[CQ Number]),'Dates'[MonthNumber]=MONTH(NOW())-1)

Either one works on their own, but not together, I am assuming it's the MONTH filter that causes the problem.

I would put a filter on the table, but in esssence I am trying to build the table so that it shows the current Invoices and Quotes, then a YTD view, and a view of the previous year period YTD.

Am I right in my assumption of the measures or not? IF so woul anyone have any solutions.

Man Thanks,

Rich

[v-nmadadi-msft]

Hi @Rich_Wyeth 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks 

[v-nmadadi-msft]

Hi @Rich_Wyeth 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Rich_Wyeth 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[bhanu_gautam]

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

[Rich_Wyeth]

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.

[v-nmadadi-msft · Accepted solution]

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