Count number of rows between transaction types
I have a calculated table that returns each purchase and sale transaction by stock ticker by date. Then I created a helper column that returns whether each row relates to a "Purchase" transaction, a "Sale" transaction, or to both ("Purchase/Sale"). ADDCOLUMNS( SUMMARIZECOLUMNS( dAssets[Ticker], dDates[Date], "Purchased", [Shares purchased], "Sold", [Shares sold], "Balance", [Shares balance] ), "Transaction", SWITCH( TRUE(), [Purchased] <> BLANK() && [Sold] = BLANK(), "Purchase", [Purchased] = BLANK() && [Sold] <> BLANK(), "Sale", [Purchased] <> BLANK() && [Sold] <> BLANK(), "Purchase/Sale" ) ) What I need is a calculated column that indicates how many rows (NEGATIVE) above each "Sale" row the last "Purchase" sale row had taken place. Obviously, in case a row refers to a "Purchase" transaction then return "null/blank". And for those rows that refer to dates on which both "Purchase" and "Sale" took place then return ZERO. One requirement must be followed: making sure such row count takes place within each group of tickers. Below is a screenshot of the table produced by the above code and I hardcoded in red the desired calculated column output. This seems simple but I just can't get a way to make it work... Right out of the gate I get the error "the column 'Transaction' cannot be found or may not be used in this expression" when trying to generate a calculated column with the Date of the last "Purchase" transaction prior to each respective "Sale". VAR Tbl = ADDCOLUMNS( SUMMARIZECOLUMNS( dAssets[Ticker], dDates[Date], "Purchased", [Shares purchased], "Sold", [Shares sold], "Balance", [Shares balance] ), "Transaction", SWITCH( TRUE(), [Purchased] <> BLANK() && [Sold] = BLANK(), "Purchase", [Purchased] = BLANK() && [Sold] <> BLANK(), "Sale", [Purchased] <> BLANK() && [Sold] <> BLANK(), "Purchase/Sale" ) ) RETURN ADDCOLUMNS( Tbl, "Last Purchase Date", VAR Ticker_Ref = dAssets[Ticker] VAR Date_Ref = MAX( dDates[Date] ) RETURN CALCULATE( MAXX( dDates, dDates[Date] ), dAssets[Ticker] = Ticker_Ref, dDates[Date] <= Date_Ref, [Transaction] = "Purchase" || [Transaction] = "Purchase/Sale" ) )
DIstinct Count of Category Name Filtered by One of Multiple Demand Columns
Hi everyone, I'm unable to get the correct distinct count of my product based on whether there is current demand, while I have two different demand columns which do not overlap in row entries. I provide simplified data in which I want it to be calculated like the following: As you can see, I want my Product column to be distinctly counted based on whether it is also populated by the Current Demand measure (this measure directly refencing a demand column in my data, since I want to create an explicit rather than implicit measure for Excel use). My thinking has been to create the following: Count=CALCULATE( DISTINCTCOUNT(ProductTable[Product],[Current Demand] <> BLANK()). However, it is not working. Please note that I don't have access to the most recent DAX functions such as DISTINCTCOUNTNOBLANK, so I hope there is still a way around this. Any help will be much appreciated.
