User Profile
andreas789
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Equivalent of SQL - NOT EXISTS in calculated column
Hello there, I have created a report that uses the products (items) table as fact table and supplier data as dimension tables, in order to find all non-available products on suppliers and change our availability flag. I have used the measure below to get the availability flag of our system. of = DISTINCT(items[AVAILABILITY_FLG]) I have also created a calculated column (on each supplier table) that will compare our & supplier's availability and if different, an action is displayed. Action = SWITCH(TRUE(), [of] = Supplier1[Status], "OK", [of] <> Supplier1[Status], "CHANGE FLAG") However, a supplier may remove discontinued products from his list and with the calculated column above, we lose that case. Is it possible to define that case as well? In SQL a NOT EXISTS operator would probably solve the problem.Calculate total sales for the last period
Hello, I am trying to calculate the sales for the last n periods in n different measures. I have the following custom calendar table. How can I create the measures that calculate the sales in the previous periods? For the month -1,-2,...,-n, I've found the following measure that works. But I can't create a measure for period -1,-2,...-n. Month - 1 QTY = VAR _index = 1 VAR _StartMonth = DATE( YEAR( TODAY() ), MONTH( TODAY() ) - _index , 1) VAR _StartNextMonth = EOMONTH( _StartMonth, 0) + 1 VAR _Amount = CALCULATE(SUM(l2y_sales[QUANTITY]) , periodsfulldata[PK_Date] >= _StartMonth, periodsfulldata[PK_Date] < _StartNextMonth) RETURN _Amount
