Totals by Month and Previous Quarter

[saie5073]

I am tracking a list of users' total sales for incentive purposes. Incentives are paid on a monthly & quarterly basis, using a point system to determine the incentive. Points are assigned as follows:

0-2 sales : 1 point
3-5 sales : 2 points
5+ sales : 3 points

For some reason I am struggling to figure out how to calculate the total number of points by month & by quarter for each user, including the quarter-to-date totals. Here is an example:

	User	Total
Jan-23	A	2
Feb-23	A	0
Mar-23	A	3
Apr-23	A	4
Jan-23	B	6
Feb-23	B	0
Mar-23	B	0
Apr-23	B	0
Jan-23	C	0
Feb-23	C	0
Mar-23	C	1
Apr-23	C	0

Any idea how I can produce these results? 

	User	Total	Points
Jan-23	A	2	1
Feb-23	A	0	1
Mar-23	A	3	2
Apr-23	A	4	2
Jan-23	B	6	3
Feb-23	B	0	1
Mar-23	B	0	1
Apr-23	B	0	1
Jan-23	C	0	1
Feb-23	C	0	1
Mar-23	C	1	1
Apr-23	C	0	1

User	1Q points	2Q points (to date)
A	2	2
B	3	1
C	1	1

[saie5073]

In my case, the quarterly amount is not a sum of the points, it is based on the sum of the total sales. For example, User A had 5 total sales in Q1, so it should be 2 points. This is where I am struggling the most. I can get the monthly totals working, but I can't figure out how to do it quarterly (and show it in the same matrix if possible). Any ideas?

[v-yangliu-msft]

Hi  @saie5073 ,

Here are the steps you can follow：

1. Create calculated column.

Month-Year =
FORMAT(
    'Table'[Date],"mmm")
    &"-"&
FORMAT('Table'[Date],"yy")

2. Create measure.

Value =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Month-Year]=MAX('Table'[Month-Year])&&'Table'[User]=MAX('Table'[User])),[Total])

Point =
SWITCH(
    TRUE(),
    [Value]>=0&&[Value]<=2,1,
    [Value]>=3&&[Value]<=5,2,3)

1Q points =
SUMX(
    FILTER(ALL('Table'),
    'Table'[User]=MAX('Table'[User])
    &&
    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
    QUARTER('Table'[Date])=1
    ),[Point])

2Q points =
SUMX(
    FILTER(ALL('Table'),
    'Table'[User]=MAX('Table'[User])
    &&
   YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
    QUARTER('Table'[Date])=2
    ),[Point])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

hope this helps.

[Greg_Deckler]

@saie5073 The first one should be something like:

Points Measure = 
  VAR __Sum = SUM('Table'[Total])
  VAR __Result = 
    SWITCH(TRUE(),
      __Sum < 3, 1,
      __Sum < 6, 2,
      3
    )
RETURN
  __Result