Formula not accepting a working measure ("a function placeholder has been used in a true/false..")

[Dave_G]

Hello everybody,

I am wondering why one of my measures is not accepting another measure for calculation.

I have created a report in which the user can choose a range of time with the between-slicer and receive the sales amount for that period. Furthermore i calculate the sales amount for the same time range previous to the chosen range.

For example if a user sets the between-slicer from 01/01/2022 to 02/28/2022 he will receive the sales amount for this period as well as for the previous period from 11/01/2021 to 12/31/2021. The calculation for each period is always based on full months.

Therefore i start by calculating the number of months the user has selected. However since all sales data is always bound to the first of each month and the other days do not contain any sales data, i have to count how many "first days of a month" the user has selected:

MonthFirst= IF(dim_Date[Date] = STARTOFMONTH(dim_Date[Date]), 1, 0)

SelectedMonths = SUM(dim_Date[MonthFirst])

So SelectedMonths tells me how many months with sales data have been selected.

If I want to calculate the sales for the previous period, i can use the following formula:

SalesPreviousPeriod_v1 = CALCULATE([Sales], PARALLELPERIOD(dim_Date[Date], fact_Sales[SelectedMonths]*(-1), MONTH))

First of all this formula does what it is expected to do, however i want to put the second parameter (PARALLELPERIOD()) in a separate measure and use the measure instead like this:

PreviousPeriod = PARALLELPRIOD(dim_Date[Date], fact_Sales[SelectedMonths]*(-1), MONTH())

SalesPreviousPeriod_v2 = CALCULATE([Sales], [PreviousPeriod])

This is not working and i get the (well known) error message "A function 'PLACEHOLDER' has been used in a true/false expression that is used as a table filter expression. This is not allowed".
Can anybody explain to me why that happens?

Thank you all very much in advance!

Dave

[v-kkf-msft · Accepted solution]

Hi @Dave_G ,

A measure formula must return a scalar value. Therefore your [PreviousPeriod] measure will not output a table.

It is also important to mention that the measure is only calculated when it is added to the visuals, so even if the measure is written without an error message does not mean that it is correct.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Dave_G , Based on what I got, Try like

same period based on date range
Last Period =
var _max =maxx(allseleceted(date),date[date])
var _min =maxx(allseleceted(date),date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date,date[date]<=_maxX &&date[date]>=_minX)))

or

same period based on date range
Last Period =
var _max =maxx(allseleceted(date),date[date])
var _min =maxx(allseleceted(date),date[date])
var datediff1 = datediff(_min,_max,Month) +1
var _maxX = Eomonth(_max,-1* datediff1)
var _minX = Eomonth(_min ,-1* datediff1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date,date[date]<=_maxX &&date[date]>=_minX)))