Regular User measure ?? DAX

[Arnaud81]

Hi guys,

Is there anyone who could help me with this ?

Let's say that a regular user is a Customer who buys :

- 10 or more units of product A in Q1.
- 10 or more units of product A in Q2 OR at least 20 units in (Q1+Q2)
- 10 or more units of product A in Q3 OR at least 30 units in (Q1+Q2+Q3)
- 10 or more units of product A in Q4 OR at least 40 units in (Q1+Q2+Q3+Q4)

I have a calendar Table with Years, Quarters, months, etc
and a Sales table with Customers, products, units, etc

Actually my problem is the conditional (OR) part of the formula.

Thanks a lot !!

Arnaud

 

[akice]

I think i understand what you are asking for. With what you have given and depending on what you put in the rows/columns of pivot (Quarters and Product type), you may be able to do it with one measure. I haven't worked up a sample to test, but something like this should work:

Regular_Users =IF (
    TOTALQTD ( SUM ( Table1[Product] ), Calendar[Date] ) > 10
        || TOTALYTD ( SUM ( Table1[Product] ), Calendar[Date] )
            > 10 * MAX ( Calendar[Quarter] ),
    "Regular User"
)

 

[Arnaud81]

Thank you akice,

This give me some directions.

I am trying to represent the number of regular users on a Graph with Power BI desktop.
This graph would show Fiscal Q1, Fiscal Q2, Fiscal Q3, Fiscal Q4. (End of FY = 31/03)

When Q1 is over, I still need to know how many regular users I had. I want to see the evolution.

If you (or someone else) have any idea how to achieve this, don't hesitate

Thanks

 

[Arnaud81]

akice,

Finally, I think that your code is almost giving me what I want. The only thing is that it gives me regular users even for future quarters.
For example, if a user purchase 40 units in Q1 then it is reported to be a regular user for Q2, Q3, Q4, which is right but is there a way to avoid this situation (no regular users after Q2 if we are in Q2 for example)