DAX: Views since last transaction

[joerykeizer]

Hi all,

I have a table which contains: Date, ProductID, Views, Transactions. 

Now I would like to see the number of views since the last transaction for each product. To calculate the last transaction date I use the following measure: [LastTransaction] = calculate(max(date);conversions>0;all(date))

For the views since last transaction I tried = calculate(sum(views);date>[LastTransaction]) but it gives me an error ("A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression."). I also tried using the Filter() command but I seemed to ignore the Date>LastTransaction statement.

I could use some help!

[Greg_Deckler]

Right, so if you expand out your formual, you essentially have:

calculate(sum(views);date>calculate(max(date);conversions>0;all(date))

So, you are essentially using CALCULATE in a Filter condition which is not allowed.

You might try using MAXX instead of CALCULATE and see if that resolves the issue. And perhaps SUMX instead of CALCULATE. I'd have to play with it. Any chance you can post sample data?

[joerykeizer]

Not sure if there is an easier way to post this but I've created & uploaded a small sample file for you to look at.

[v-sihou-msft · Accepted solution]

@joerykeizer

In this scenario, you can use FILTER() to filter dates later that you calcuated Last Transaction Date. The expression can be like:

ClicksSinceLastTransaction = CALCULATE(SUM(Sheet1[Views]),FILTER(ALL(Sheet1[Date]),Sheet1[Date]>Sheet1[LastTransaction]))

I also attached modifed .pbix file

[joerykeizer]

Thank you very much, I tried the filter but messed up the All(Date) part, I get it now.