Forum Discussion
Trouble replicating Excel formula in Power BI
I'm trying to replicate an Excel formula in Power BI. The formula gives me a running average for a selected metric. The Excel formula is: Goal=SUM(range)/((YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)+1), with LDate (Later Date) = refresh date, and EDate (Earlier Date) = start date.
In Power BI I've tried replicating this with Goal = Calculate(SUM('Main Daily Data'[Page Views]),((Year(Today())-Year(Date(2018,5,2)))*12+Month(Today))-Month(Date(2018,5,2))+1).
When I run this formula in Power BI I get an error that reads," The True/False expression does not specify a column. Each True/False expression used as a table filter must refer to exactly one column. Not sure where to go. If anyody can help, it would be apprechiated.
Hi,
The second input of the CALCULATE() function should be a column or expression that you would like to filter. Your second input is just an expression so that formula does not know which already written expression or base data column to apply a filter condition on.
1 Reply
- Ashish_Mathur
Super User
Hi,
The second input of the CALCULATE() function should be a column or expression that you would like to filter. Your second input is just an expression so that formula does not know which already written expression or base data column to apply a filter condition on.