First Order Revenue by Date

[honzafelt]

Hello fellow analysts,

I have a this dataset and want to find the revenue coming from the first purchase (the Price column) and plot it on a bar chart by date. I also want to know how much revenue comes from first purchases versus returning purchases every day. Here is how I went about it.

Using this formula, I determined the Measure for First Order Revenue.

First Order Revenue = SUMX(
    VALUES(test[UserID]),
    CALCULATE(
        MIN(test[Revenue]), FIRSTDATE(test[Date])
    ))

Then I created another Measure for revenue coming from all other orders using this formula.

All Other Revenue = SUM(test[Revenue])-[First Order Revenue]

I can now easily visualise the breakdown of first order revenue vs. returning orders revenue according to user ID.

However, I can't see the breakdown of all other revenue vs. first order revenue by date. The chart shows me total revenue instead.

Could you please tell me where have I made the mistake (and what the mistake is)? I have a feeling it has something to do with the structure of the first calculation.

Thank you,

Honza

[v-sihou-msft · Accepted solution]

@honzafelt

In your scenario, this "FirstPurchase" is always group on each User Level. When analyzing on Date Level, there's no "FirstPurchase" to each day. For your requirement, I suggest you add a flag column to tag if an entry is FirstPurchase, then put this Flag column into Legend. 

IsFirst = IF(test[Date]= CALCULATE(MIN(test[Date]),ALLEXCEPT(test,test[UserID])),"FirstPuchase", "ReturnPurchase"  )

Regards,

[honzafelt]

This is exactly what I needed. Thank you so much, @v-sihou-msft! I've got a long way to go. 

[Ashish_Mathur]

Hi,

I am not clear with your question.  Please share the overall objective and show the expected result.